Specific Query Performance Tips

Below are the tips

Ø  Use EXPLAIN to profile the query execution plan

Ø  Use Slow Query Log 

Ø  Don't use DISTINCT when you have or could use GROUP BY

Ø  Insert performance

o   Batch INSERT and REPLACE

o   Use LOAD DATA instead of INSERT

Ø  LIMIT m, n may not be as fast as it sounds. Learn how to improve it and read more about Efficient Pagination Using MySQL

Ø  Don't use ORDER BY RAND() if you have > ~2K records

Ø  Use SQL_NO_CACHE when you are Selecting frequently updated data or large sets of data

Ø  Avoid wildcards at the start of LIKE queries

Ø  Avoid correlated sub queries and in select and where clause (try to avoid in)

Ø  No calculated comparisons -- isolate indexed columns

Ø  ORDER BY and LIMIT work best with equalities and covered indexes

Ø  Separate text/blobs from metadata, don't put text/blobs in results if you don't need them

Ø  Derived tables (sub queries in the FROM clause) can be useful for retrieving BLOBs without sorting them. (Self-join can speed up a query if 1st part finds the IDs and uses then to fetch the rest)

Ø  ALTER TABLE...ORDER BY can take data sorted chronologically and re-order it by a different field -- this can make queries on that field run faster (maybe this goes in indexing?)

Ø  Know when to split a complex query and join smaller ones

Ø  Delete small amounts at a time if you can

Ø  Make similar queries consistent so cache is used

Ø  Have good SQL query standards

Ø  Don't use deprecated features

Ø  Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index merge should pick stuff up.

Ø  Don't use COUNT * on tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()

Ø  Use INSERT ... ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT

Ø  use group wise maximum instead of sub queries

Ø  Avoid using IN (...) when selecting on indexed fields, It will kill the performance of SELECT query.

Ø  Prefer using UNION ALL if you don't need to merge the result