MySQL 5.0 introduces a new “greedy” optimizer which can greatly reduce the time required to arrive at a query execution plan. This is particularly noticeable where several tables are to be joined and no good join keys can otherwise be found. Without the greedy optimizer, the complexity of the search for an execution plan is calculated as N!, where N is the number of tables to be joined. The greedy optimizer reduces this to N!/(D-1)!, where D is the depth of the search. Although the greedy optimizer does not guarantee the best possible of all execution plans (this is currently being worked on), it can reduce the time spent arriving at an execution plan for a join involving a great many tables — 30, 40, or more — by a factor of as much as 1,000. This should eliminate most if not all situations where users thought that the optimizer had hung when trying to perform joins across many tables.

Use of the Index Merge method to obtain better optimization of AND and OR relations over different keys. (Previously, these were optimized only where both relations in the WHERE clause involved the same key.) This also applies to other one-to-one comparison operators (>, <, and so on), including = and the IN operator. This means that MySQL can use multiple indexes in retrieving results for conditions such as WHERE key1 > 4 OR key2 < 7 and even combinations of conditions such as WHERE (key1 > 4 OR key2 < 7) AND (key3 >= 10 OR key4 = 1). See Section 7.2.6, “Index Merge Optimization”.

A new equality detector finds and optimizes “hidden” equalities in joins. For example, a WHERE clause such as

t1.c1=t2.c2 AND t2.c2=t3.c3 AND t1.c1 < 5

implies these other conditions

t1.c1=t3.c3 AND t2.c2 < 5 AND t3.c3 < 5

These optimizations can be applied with any combination of AND and OR operators. See Section 7.2.11, “Nested Join Optimization”, and Section 7.2.12, “Outer Join Simplification”.

Optimization of NOT IN and NOT BETWEEN relations, reducing or eliminating table scans for queries making use of them by mean of range analysis. The performance of MySQL with regard to these relations now matches its performance with regard to IN and BETWEEN.

The VARCHAR data type as implemented in MySQL 5.0 is more efficient than in previous versions, due to the elimination of the old (and nonstandard) removal of trailing spaces during retrieval.

The addition of a true BIT column type; this type is much more efficient for storage and retrieval of Boolean values than the workarounds required in MySQL in versions previous to 5.0.

Performance Improvements in the InnoDB Storage Engine:

(See Section 13.2, “The InnoDB Storage Engine”.)

Performance Improvements in the NDBCLUSTER Storage Engine:

(See Chapter 17, MySQL Cluster.)