Join 8000 others and follow Sean Hull on twitter @hullsean.
SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code.
Here’s a few quick tips to write tighter queries in MySQL
1. Get rid of those Subqueries!
Subqueries are a standard part of SQL, unfortunately MySQL doesn’t handle them very well. Luckily there’s a sweet rewrite that can put you in the fast lane. Here’s how to speedup a MySQL subquery by rewriting as a join.
Note that another compelling reason to upgrade to MySQL 5.6 is that this tweak has been rolled into the optimizer. Hoorah!
2. Repair those UNIONs
If your code uses the UNION construct in SQL, there are a few different ways to tune those queries. You can use UNION ALL or pushdown conditions can help you optimize UNION in MySQL.
4. Better PAGING through datasets
Does your web application display pages of users, pages of orders or pages of items? If you’re using
LIMIT and OFFSET there are 3 good ways to optimize these in MySQL.
Check out: Scalability Happiness – A Quiet Query Log