Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

3 Simple Patterns for Tighter MySQL Code

DZone's Guide to

3 Simple Patterns for Tighter MySQL Code

· Performance Zone
Free Resource

Transform incident management with machine learning and analytics to help you maintain optimal performance and availability while keeping pace with the growing demands of digital business with this eBook, brought to you in partnership with BMC.


Join 8000 others andfollow 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.GetridofthoseSubqueries!

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.RepairthoseUNIONs

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.BetterPAGINGthroughdatasets

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.

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Sean Hull. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}