Top 10 SQL Performance Tips
Hmm... lools like my love affair with SQL related posts continues.
MySQL has posted an excellent resource posted called Top 10 SQL Performance Tips. Although, it is kind of a misleading name, since its a wiki, and everybody is able to post. Currently it's up to 84 tips, most of which look really great. Here is a small sampling:
- Don't use DISTINCT when you have or could use GROUP BY
- avoid wildcards at the start of LIKE queries
- Use a clever key and ORDER BY instead of MAX
- (And my personal favourite)... Don't use ORDER BY RAND() if you have > ~2K records :)
If you actually keep local copies of your database and then upload, you can also take advantage of suggestions such as "Use EXPLAIN to profile the query execution plan" among others. A great resource.
John wrote on
Also, don’t store images in the database. Instead, store file names and upload the images to the server. That way, you can just pull the path and image without needing to store a huge blob file in your database.