Understanding MySQL s Built-In Query Optimization


Understanding MySQL's Built-In Query Optimization

MySQL applies many optimization rules to queries.

MySQL uses its estimated number of rows (as shown in EXPLAIN ) to work out the best order in which to join tables. If you notice that its estimate is off, you may want to experiment with using a STRAIGHT JOIN to force the table order. Benchmarking before and after cases will tell you whether you are helping or hindering .

To choose an index, MySQL looks for the most relevant index that spans less than 30% of the rows. If it can't find an index fitting these criteria, the table will be scanned instead. (This was what happened in the EXPLAIN query we looked at earlier, after we added the new index.)

Expressions in WHERE clauses are optimized in a similar way to the way many programming compilers optimize expressions. For example, unnecessary parentheses in expressions are removed. This is one reason you should feel free to make your queries more readable with parentheses.

If a query can be resolved wholly from indexes, it will be done without any reference to the actual rows in the table. Evaluation of COUNT(*) is also evaluated without reading or counting the rows in a table because this data is stored separately.

A more comprehensive list of the optimizations MySQL performs is in the manual, so we have not reproduced it here. But even that list is incomplete. You can read through the source code if you are interested in more information about how the query optimizer works.



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net