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.