This stuff has helped improve many of my queries.
The basics:
- Its bad whenever you have the second table in the explain returning more than 1 row. The driving table is first and all other tables joined to it should be by a key with a single match.
- The optimizer may change the order of tables based on the data.
- Limit will not change the time to process when explain says ‘using where’. This is because, since it is not using an index, it has to inspect all records first.
Things to try:
- One way is to use the ‘straight_join’ hint which will force mysql to join the tables in the order in the query. This is not optimal however because as the data content in the table changes, a different execution plan may become optimal and still mysql will be forced to join in the way the query is written.
- A second try would be to add an index on the fields in the where clause. Depending on the cardinality of these fields (# of distinct values), an index on one or all will perform best. Not knowing the cardinality, the most probable index to benefit will be the concatenated index on both these columns, with the column with least cardinality (number of distinct values) first.
- You can avoid filesort if the order by column is also in the where. Try ordering by a column that is also the first column in the index.
- Its much better if you can use > or < so that the optimizer has a chance at using an index.
See also http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/
