Monday, April 27, 2009

MySQL 5.4

New Features

MySQL has released a new version of database server – MySQL in version 5.4. Latest release was 5.1 with a really great features. This one brings not so much but also very important features:

  • InnoDB storage engine can address more than 4 CPU’s/cores. Thjs helps that MySQL scale much better under huge application workloads.
  • Till now, subqueries were well known as performance problematical. In version 5.4 subquery optimization has been improved a lot in a number of various use cases. As MySQL mentioned in the example below, execution time dropped from 12 minutes (9 000 000 reads) on MySQL 5.1 to just 1.8 seconds (153 000 reads) on MySQL 5.4
SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
l_orderkey IN
(SELECT o_orderkey FROM orders
WHERE o_totalprice > 1000 AND
o_custkey IN
(SELECT c_custkey FROM customer
WHERE c_address LIKE 'Le%'));
  • MySQL 5.4 offers a new much better join algorithm which speed up execution time of multi-way joins like in the following example
SELECT COUNT(*) FROM part, lineitem
WHERE l_partkey=p_partkey AND p_retailprice>2050
AND l_discount>0.04;
  • Improved error handling – through the implementation of standard SQL (SQL 2003) SIGNAL and RESIGNAL operations in stored procedures, functions and triggers developers are able to signal rule violations during execution.

Must mention here that Oracle and SQL Server not implement this functionality but have their own mechanisms: Oracle through RAISE and SQL Server through RAISEERROR statements.

  • INFORMATION_SCHEMA got a new objects PARAMETERS. We wait a lot on this.

  • In prior version of MySQL out parameter were not possible in prepared statements. That has been changed now.

This is all from programmers perspective. Have a fun.

Compare SQL Server, Oracle and MySQL database with DBTYP.NET Studio

No comments: