From: Michael Widenius Date: June 12 2001 5:08pm Subject: Myql internals List-Archive: http://lists.mysql.com/internals/903 Message-Id: <15142.19590.135660.212374@narttu.mysql.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! >>>>> "Dip" == Dip writes: Dip> Hi All, Dip> Im running some benchmarking tests on mysql, postgres and interbase Dip> database servers. Dip> does anyone know the reasons why or know where i can find out some Dip> technical reasons why the mysql database server is particularly good in Dip> relation to the others or just by itself e.g. use of query optimisation Dip> or indexing etc. I'm only looking at creates, selects, inserts, update Dip> and delete statements. I've noticed that Mysql is particularly good at Dip> insertions, deletes and updates but quite poor (in relation to postgres Dip> and interbase) at performing complex queries such as left, right, inner Dip> joins with where clauses. Why is this? The reason is that most of the MySQL core is programmed by one persons with the goal to make everything as fast as possible; By having a person that knows all code you can get all code consistent and avoid duplicated code. Most of the code has been used and fine tuned in a high volume production environment for over 20 years. By not having transactions enabled by default, you can do a lot of shortcuts and optimizations that are not possible with a standard SQL database. The SQL engine in MySQL also includes a lot of optimizations that most other databases doesn't do. MySQL also uses threads which in most cases give MySQL a speed advantage. We have tried to test most types of queries with MySQL and PostgreSQL and for most cases MySQL has been 2-5 times faster. (This includes complex queries with left, right and normal joins) Please check the benchmarks at: http://www.mysql.com/information/benchmarks.html You can find a list of the majore differences between MySQL and PostgreSQL at: http://www.mysql.com/doc/C/o/Compare_PostgreSQL.html What kind of queries have you found faster with PostgreSQL ? Any change you could get time to add a test in our benchmark suite that shows when PostgreSQL is better ? Dip> I've tried to look around for this on the net, but all i seem to get is Dip> a big arguement consisting of the features lacking in mysql that are in Dip> other databases e.g. ACID!! You can with MySQL use BDB or InnoDB tables and to get MySQL ACID. Regards, Monty