Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google Log In with Steam Sign In
  • Create Account
Photo

Query Performance Tips

- - - - -

  • Please log in to reply
No replies to this topic

#1
OFFLINE   hellreturn

hellreturn

    Management

  • Management
  • PipPipPipPip
  • 2693 posts
158
On the road to fame
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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users