Posted by Erwin Moller on 01/22/08 08:59

Toby A Inkster wrote:
> Erwin Moller wrote:
>
>> I never understood how MySQL gained their huge marketshare, given the
>> fact that PostgreSQL was there, ready-to-use, and free for so many
>> years. Unless it is MySQL created a Windows version earlier than
>> PostgreSQL did, so new developers (who only knew how to boot into
>> Windows) picked MySQL.
>
> The Windows version gave MySQL a big boost, but it's not only that.
>
> Firstly, PHP and MySQL were well-integrated from an early stage. The
> integration between PostgreSQL in PHP is now just as good, but it wasn't
> for a while. The growths of MySQL and PHP reinforced each other.
>
> Secondly, PostgreSQL hasn't been around *that* long. It has a history that
> goes back before MySQL, sure, but before 1997 it wasn't called PostgreSQL
> -- it was called Postgres. The earlier Postgres system was a relational
> database system that did *not* support SQL. SQL support was slowly added
> between 1994 and 1997.
>
> By the time Postgres became PostgreSQL, mSQL already had a strong foothold
> in the mid-size UNIX database market. mSQL wasn't open source, but it was
> fairly entrenched. So when an open source alternative (MySQL) came along,
> offering full API compatibility with mSQL, it is no surprise that it
> became immediately popular.
>
> Lastly, but not least: performance. No, MySQL is not ACID-complete, and it
> still has a long way to go to catch up with PostgreSQL, but it's always
> been fast. As MySQL has started adding features, it has started to slow
> down a little; and recent releases of PostgreSQL have made massive speed
> improvements too; so the difference is becoming negligible. But for most
> simple queries, MySQL is usually still slightly faster.
>
> MySQL achieves a lot of its speed advantages by cutting corners in data
> integrity checks. (For example, even in current versions of MySQL,
> configured using default settings, it is possible to record nonsense dates
> such as the 30th of February.)
>
> For complex queries, PostgreSQL tends to do better. MySQL 5 still isn't
> bad. MySQL 4 and below just barf and say they don't understand the query.
> If you've got shed-loads of data and you're into serious optimisation, you
> can probably make PostgreSQL go faster than MySQL, because it has much
> better indexing facilities.
>
> For example, say you want to frequently do searches like this:
>
> SELECT telephone
> FROM contacts
> WHERE LOWER(forename||' '||surname) LIKE 'john do%';
>
> (And don't get me started on MySQL's incorrect handling of the '||'
> operator, and its general butchering of standard SQL syntax!) then on
> PostgreSQL, you can create an index on an arbitrary expression:
>
> CREATE INDEX fullnames
> ON contacts (LOWER(forename||' '||surname));
>
> MySQL cannot create indexes on expressions -- only on columns.
>

Hi Toby,

Thanks for the historical perspective on mySQL's popularity.
I didn't know SQL support was added that late (from 1994 onto 1997).
I think I started using PostgreSQL around 1997, so that was a good year
in retrospect. ;-)

But the rest of your response only underlines why PostgreSQL is better
in so many respects than MySQL. ;-)
Anyway, I'll simply stick to PostgreSQL unless I am forced otherwise, in
which case I always use an abstractionlayer (ADODB) to ease the pain.

Regards and thanks for your response,
Erwin Moller

[Back to original message]