Optimizing Slow Queries

From Hashmysql
Jump to: navigation, search

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.