List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 6 1999 12:36pm
Subject:PostgreSQL vs Mysql comparison
View as plain text  
Hi!

Scott> I have pasted the full text below relative to MYSQL AND POSTGRESQL
Scott> let's try to use this as a working document and update it.
Scott> Thanks, Scott
  
Some comments:

Scott> Low-Cost Unix Database Differences   - Author Unknown

Scott> MySQL: 
Scott> -------
Scott> Pros: 
Scott> * Extremely fast. 
Scott> * Regular expression support. 
Scott> * Quite a few datatypes available. 
Scott> * Support staff very knowledgable and helpful. 
Scott> * Supports identity/auto-increment columns, similar to sequences. 
Scott> * C-based API very similar to Msql; aids in porting Msql apps to MySQL. A small
Scott> shell script is included that does the basic port ( via sed ) 
Scott> * CHAR/VARCHAR/TEXT columns handled canse insensetively. 
Scott> * Low-cost, often free, for most users. Commercial support available. 
Scott> * BLOB support stores the BLOBs in the table. 
Scott> * Supports user-defined functions in C and derivatives. 
Scott> * Multithreaded

Scott> CONS:
 
Scott> * No support for transactions ( begin transaction, rollback, commit ). One must
Scott> explicitly lock a table and unlock it when finished. 
Scott> * No foreign key support. 
Scott> * No trigger support. 
Scott> * No subselects. 
Scott> * No views. 
Scott> * No sequence support. No, auto_increment columns are not sequences. 

Note that sequences is not defined by ANSI SQL;  AUTO_INCREMENT colums 
are about as much standard than sequences.
Note that you can VERY easily emulate sequences in MySQL

Scott> * Auto_increment columns will give the next available number upon insert,
Scott> possibly messing up what little referential integrity constraints your
Scott> application attempts to enforce. 

If you write your application properly, it's note a problem even if one
reuse numbers that is not in use.  In MySQL 3.23 you will also always get a 
new auto_increment number on insert.

Scott> * Only one automatically-updated column per table; one cannot have a timestamp
Scott> and auto_increment column in the same table and have both be updated. 

The above is not true. There has never been a problem with having a
timestamp and a auto_increment column in the same table.

Scott> * User-defined functions, even the most basic ones, must be in C and
Scott> derivatives. 
Scott> * No on-line recovery; one must bring down the database server and run the
Scott> 'isamchk' utility on their datafiles. 

OPTIMIZE table should be comparable to VACUM on postgreSQL.

Scott> * Hacked-up SQL functions such as REPLACE INTO. 

Scott> * Indexes must be created at table-creation time. To add a new index/key, the
Scott> table (and its data) must be backed up and reimported. Work around: create a
> new
Scott> table, insert into new_table select * from old_table, drop old_table, alter
Scott> table new_table rename old_table. 

The above was fixed 2 1/2 years ago.

Scott> * Does not handle dates correctly. One can insert '1999-02-31 01:01:01' into a
Scott> datetime column, mysql accepts it. 

We regard this as a feature, not as a bug.  It's not the databases job 
to validate data by default, only to store and retrieve it.

Note that MySQL 3.23 allows you to use 'fuzzy' dates: '1999-02-00'.
This is VERY useful when you store birth-days/death-days!

Scott> * Does not handle date manipulation properly; select '1999-08-14 11:32:00' - 7
Scott> == '1992' 

This is because the above means:

select (convert to int) '1999-08-14 11:32:00' - 7 

-> 

select 1999 - 7.

Because '1999-08-14 11:32:00' is not a date object, but a string.

You should instead use:

SELECT DATE_SUB('1999-08-14 11:32:00',INTERVAL 7 DAYS)

or even

SELECT '1999-08-14 11:32:00' - INTERVAL 7 DAYS

Regards,
Monty
Thread
PostgreSQL vs Mysql comparisonScott Perkins2 Oct
  • Re: PostgreSQL vs Mysql comparisonPatrick Greenwell2 Oct
    • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker4 Oct
      • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonPaul DuBois4 Oct
        • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker4 Oct
          • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonrandyboy4 Oct
            • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker4 Oct
              • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonBruce Momjian4 Oct
                • Re: PostgreSQL vs Mysql comparisonMartin Ramsch4 Oct
                  • Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker4 Oct
                    • Re: PostgreSQL vs Mysql comparisonTonu Samuel4 Oct
                    • Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
                  • Re: PostgreSQL vs Mysql comparisonBruce Momjian4 Oct
                    • Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
                      • Re: PostgreSQL vs Mysql comparisonBruce Momjian6 Oct
                        • Re: PostgreSQL vs Mysql comparisonMichael Widenius7 Oct
                          • Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker7 Oct
                            • Re: PostgreSQL vs Mysql comparisonBruce Momjian7 Oct
                              • Re: PostgreSQL vs Mysql comparisonMichael Widenius7 Oct
                                • Re: PostgreSQL vs Mysql comparisonMatthias Urlichs7 Oct
                        • Re: [GENERAL] Re: PostgreSQL vs Mysql comparisonAaron J. Seigo7 Oct
                      • Re: PostgreSQL vs MySQL benchmark...Dimitri24 Jul
                  • Re: PostgreSQL vs Mysql comparisonBruce Momjian4 Oct
                    • Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
                      • Re: PostgreSQL vs Mysql comparisonBruce Momjian6 Oct
                • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonPaul DuBois4 Oct
                  • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonBruce Momjian4 Oct
                    • Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonDuncan Kinder4 Oct
                      • Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker4 Oct
                        • Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonDuncan Kinder4 Oct
                    • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
              • PostgreSQL vs Mysql comparisonScott Perkins4 Oct
                • Re: [GENERAL] PostgreSQL vs Mysql comparisonBruce Momjian4 Oct
                • Re: PostgreSQL vs Mysql comparisonLuuk de Boer4 Oct
                • PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
              • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonLuuk de Boer4 Oct
          • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonAaron J. Seigo4 Oct
            • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
              • Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonRasmus Lerdorf6 Oct
              • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonAaron J. Seigo7 Oct
          • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonLuuk de Boer4 Oct
          • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
            • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonThe Hermit Hacker7 Oct
              • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonMichael Widenius7 Oct
          • Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparisonTom Dryden7 Oct
        • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonPatrick Greenwell4 Oct
      • Re: [PHP3] Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
    • Re: PostgreSQL vs Mysql comparisonMichael Widenius6 Oct
  • Re: PostgreSQL vs Mysql comparisonJames Manning2 Oct
Re: PostgreSQL vs Mysql comparisonBruce Momjian7 Oct