Re: Independent comparison of PostgreSQL and MySQL
От | Thomas Kellerer |
---|---|
Тема | Re: Independent comparison of PostgreSQL and MySQL |
Дата | |
Msg-id | m147ij$oid$1@ger.gmane.org обсуждение исходный текст |
Ответ на | Independent comparison of PostgreSQL and MySQL (Stephen Cook <sclists@gmail.com>) |
Ответы |
Re: Independent comparison of PostgreSQL and MySQL
(Thomas Kellerer <spam_eater@gmx.net>)
Re: Independent comparison of PostgreSQL and MySQL (Kevin Grittner <kgrittn@ymail.com>) |
Список | pgsql-advocacy |
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
В списке pgsql-advocacy по дате отправления: