Query Performance Tips

[hellreturn]

1. Use Slow Query Log (always have it on!)

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

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

4. Use SQL_NO_CACHE when you are selecting frequently updated data or large sets of data

5. Avoid wildcards at the start of LIKE queries. It kills the performance on large size of DB.

6. Avoid correlated subqueries and in select and where clause (try to avoid in)

7. No calculated comparisons -- isolate indexed columns

8. ORDER BY and LIMIT work best with equalities and covered indexes

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

10. Derived tables (subqueries 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)

11. Know when to split a complex query and join smaller ones

12. Delete small data at a time if you can to avoid table locks.

13. Make similar queries consistent so cache is used

14. Have good SQL query standards

15. 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.

16. Don't use COUNT * on Innodb 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()

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

18. Use stored procedures to avoid bandwidth wastage.