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!
Also: 5 Things Deadly to Scalability.
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.
Read this: MySQL DBA Hiring Guide for candidates, managers & recruiters
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