Posted by Toby A Inkster on 01/18/08 21:08

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.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 19 days, 4:02.]

Ham vs Bacon vs Pork
http://tobyinkster.co.uk/blog/2008/01/17/pork-etc/

[Back to original message]