3 Simple Patterns for Tighter MySQL Code

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

Get some in your inbox: Exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

  • Rolf Martin-Hoster

    The devil is in the details on the 5.6 subquery optimization, I highly recommend everyone rtfm before assuming blanket fixes!

    • http://www.iheavy.com/blog/ Sean Hull

      Thx Rolf. Yeah that damn devil is always at it, messing up a good thing. :-)

  • Marc Sherwood

    For those times when the subqueries are out of your hands have a look at MariaDB as well. The changes to the optimizer make subqueries not just possible, but FAST!