Re: Independent comparison of PostgreSQL and MySQL

Stephen Cook wrote on 08.10.2014 20:26:
> Hello!
>
> I have a client who is looking to move from SQL Server to MySQL,
> however they are open to considering PostgreSQL instead.
>
> Can anyone link me to any white papers, studies, comparisons, etc
> that are independent / unbiased (i.e. not written by MySQL or
> PostgreSQL organizations)?
>

I maintain a high level feature comparison here:

    http://www.sql-workbench.net/dbms_comparison.html

There are several quirks in MySQL which might make real life harder than a plain feature comparison might express.

One of the really annoying things is that it actually lies about what it is doing.
Officially it does not support a full outer join, and something like:

   select  *
   from t1
     full outer join t2 on t1.id = t2.id

will be rejected with an error message (which is acceptable)

But, using a slightly different syntax:

   select *
   from t1
     full join t2 using (id);

the outer join is accepted(!) but it is silently executed as an inner join

   http://sqlfiddle.com/#!9/96d1e/2

It's locking behaviour is also a bit weird. Take the following example:

   create table foo
   (
      id integer not null primary key,
      c1 integer not null
   );


Then insert 10 rows into that table (id = 1...10) and some random values into c1.

Then in one session (autocommit off) do this:

   update foo
     set c1 = c1 + 1
   where id between 1 and 5;

and in a second sesson do this:

   update foo
     set c1 = c1 + 1
   where id between 6 and 10;

The second session is updating completely different rows than the first one, yet it is blocked by the first one
nevertheless(using InnoDB which is supposed to do row level locking) 


But my "favorite" example, is this:

    delete from orders
    where '1x';

    --> deletes all rows from the table


    delete from orders
    where 'abc';

    --> will not delete anything


The lack of modern features like window functions or recursive queries might seem like just a little annoyance, but I
haveseen queries that had to work around that, which ran several times slower on MySQL than the comparable solution
usinge.g. window functions. 

If you monitor Stackoverflow, you'll notice that questions regarding MySQL that might require recursive queries pop up
therevery frequently 
http://stackoverflow.com/questions/tagged/recursive-query+mysql

So that isn't an "exotic" feature.

Or the "greatest-n-per-group" problem which can most of the time be solved quite efficiently using window functions:
http://stackoverflow.com/questions/tagged/mysql+greatest-n-per-group


Thomas