Optimizing Slow Queries

There are a number of reasons you can easily check.

• No index on a field that is in a where clause. To check see whether your query uses an index see Using EXPLAIN.

• MySQL decides it will be quicker to do a full table scan

• You are using a function that requires a full table scan

• Your database and tables need normalising

• You have a limit clause after the select. MySQL will get the entire result set before cutting and returning the limit clause.

I.E. with a table that has a primary index and has 11 million rows then SELECT * from table limit 10900000,30 will be slow as the index is not used because A. MySQL has decided that more than 50% of the table is needed in the result therefore the index will not be used B. The whole 11 million rows are read before the limit is applied.