SQL Performance Tuning: 15 Go-To Tips to Fix Slow Queries

By Doug Breaker

Date April 6, 2019

Your boss calls it "sequel," but you've been calling it S-Q-L for months. And that’s not the worst. You're suffering from "Super Slow Query Syndrome,” and sometimes, your queries bomb without a result.

Don’t worry. The SQLBot team has your back. We just had an overcaffeinated powwow to brainstorm our favorite tips to fix grinding slow queries.

Basically, we combined all our legendary SKILLZ into one, so you can level up your SQL wisdom in six minutes flat...

Before You Write a Query

The first tips happen before you start writing queries:

1) Clarify Your Information Needs:

If you don’t clarify your information request, the people who want the information will keep sending you back for more data. So before writing SQL queries, ask the following:

Who wants the data? Write down the names of all the parties who want the data. The fewer the better. These people should be on the same page—so you know the exact information needs—prior to querying the database. Otherwise, they’ll come knocking on your door, and you’ll have to rewrite the queries.
What information do they want? Unless you enjoy wasting time, pinpoint what the interested parties want, and double-check the list with them before designing your queries.
What level of detail do they require? Sometimes, upper management wants a birds-eye view. Other times, the engineers want a detailed investigation. Understanding the amount of detail tells you how to design your queries.
Why are they asking for this information? Knowing “why” you’re querying the database helps you achieve the desired results. If the dots don’t connect, follow up for clarification. This will reveal the pointless requests, which you’ll get a lot of. Don’t be afraid to ask, “Do you really need to expend resources to get this dataset?”

Let’s recap: Define the who, what, and why of the data request. Then, double-check the information with the relevant stakeholders. After that, you can explore “how to write your queries” more efficiently.

SQL Status Update #1: Great job getting this far! You reached SQL Status Level 20: UNCOMMON

Making Your Queries More Efficient

The next tips relate to the way you write queries:

2) Check the WHERE Clause:

The WHERE clause connects two or more columns from different tables. This sets up a temporary composite table of data that satisfies the join condition.

Here’s the tip: The WHERE clause and related table joins are vital for filtering the elements of your SQL query. If there’s a problem, check this clause first. The problem could be that you’re joining tables on unindexed table fields. That will bog things down because the system will check every record in the tables your referenced instead of using the index to find specific records.

One more tip: The fastest results come when the WHERE clause uses indexed table fields.

3) Check the GROUP BY and ORDER BY Clauses:

GROUP BY clauses build a single row of query results for the categories you specify. The groups are sets of rows with the same values for all columns of expression referenced in the GROUP BY clause. For example, you could group sales by customer.

ORDER BY clauses organize query results. For example, you can ORDER BY date in ASC (ascending) or DESC (descending) order. Or, ORDER BY.

Here’s the tip: Both the GROUP BY and ORDER BY elements cause problems when they don’t accurately reflect the design of your tables and indexes. So consider the design of the tables and indexes before you write your GROUP BY and ORDER BY statements. Got it?

4) Zoom Out to Consider All Data Requests:

SQL performance tuning means you have to see the forest and the trees. You can’t work on your queries without keeping the tables and indexes in mind.

Here’s the tip: After you’ve sorted out the tables and indexes, zoom out to consider all essential queries. This process might feel tedious, but looking closely at more queries will give you the “big picture.” And that will help you create better indexes for your queries.

5) Merge Indexes and Delete the Ones You Don’t Need:

Too many indexes will also shoot you in the foot by making your write operations sluggish (what a grind!). For example, when you INSERT data and UPDATE data, too many indexes will slow you down.

Here’s what to do: Always try to merge as many indexes as you can and delete the indexes you don’t need.

One more tip: Every time you want to add a new index to enhance your SQL queries, think about it carefully. Is the index worth it? If it is, are you creating the index right way? If not, you’re just making more problems.

6) Define Your Asterisk!

Everyone’s trying to cover their “as*terisk” at work, but you also need to define it! SQL newbies love using an asterisk to define their SELECT fields as “select all” like this:

SELECT * FROM global_smartphone_sales

But this will query all the data from the table. If the data has tons of fields and rows, “select all” will tax the database resources and slow the entire system down. Your query will grind to a halt: data overload.

Fix it like this: Instead of “select all,” use the SELECT statement to define the specific fields you need to query. Here’s what it looks like:

SELECT phone_type, state, buyer_name, purchase_time FROM global_smartphone_sales

This allows you to query only the fields that contain the needed information. “Defining your asterisk” should speed things up immediately.

7) Instead of Using SELECT DISTINCT Just SELECT Additional Fields:

SELECT DISTINCT lets you delete duplicate information from your query. It GROUPs the fields in your query to provide more specific information. Here’s what it looks like:

SELECT DISTINCT phone_type, city FROM global_smartphone_sales

SELECT DISTINCT is useful, but it can tax the system because it requires a lot of computing power. It can also group the data too much, causing erroneous results.

For example, the query above fails to consider that multiple cities around the world have the same name—like Melbourne, Australia, and Melbourne, Florida. The query merges the data for these cities as if they were the same place, giving you the wrong information.

Here’s the tip: You can save computing power and still deliver distinct results by SELECTing additional fields in the query instead of using SELECT DISTINCT. Here’s how it looks:

SELECT phone_type, city, zip_code, county, state_province_district, country FROM global_smartphone_sales

8) Use LIMIT Statements to Check if Your Query Involves Too Much Information:

Rather than blindly running a query, use a LIMIT statement first to make sure the query produces a manageable result—especially when working with a giant database. A LIMIT statement prevents you from accidentally overburdening and shutting down the system because it reveals if your query involves too many records.

Here’s the fix: Test your query by inserting a LIMIT statement like this:

SELECT phone_type, state, buyer_name, purchase_time FROM global_smartphone_sales WHERE YEAR(purchase_time) = ‘2015’

LIMIT 2000

If the query involves more than 2000, the “LIMIT 2000” will only bring back the first 2000 records. Why bring back more than you need? Using the LIMIT function can really help when crafting your query.

9) Analyze Your Slow SQL Queries with This Free Tool:

You can improve your query efficiency by using a tool to analyze and optimize your SQL queries. SQL query planning tools look for performance problems and give you data to better understand how your queries are measuring up.

Here’s the tip: ApexSQL Plan is a free SQL query planning tool that provides valuable information. It’s kind of like taking your queries to a personal trainer, so make sure you check it out!

10) Run Your Toughest Queries Overnight:

When you have a monster query and there’s no way to streamline it, ask if you can run it during off-peak hours—usually between 3 and 5 a.m.—or over the weekend.

Here’s when this works best: Running a query overnight is best when queries have: looping statements, cartesian JOINs, more than one schema query, complicated wildcard searches, SELECT DISTINCT statements, CROSS JOINs, and anything else that will burden the system.

SQL Status Update #2: YaaaaaaH! You’re doing great. Your SQL knowledge is solid! You've reached SQL Status Level 40: RARE.

Trouble-Shooting Failed Queries

If the database rejects your query, it could feel like you’re up against a brick wall. Here’s some wisdom:

11) Make Sure It’s Not a Function Call Problem:

Imagine you’re working for Steve Wozniac of Apple lore. The Woz wants to know how many smartphones were sold in 2015. You absent-mindedly plug in the SQL query like this:

SELECT count(*) FROM global_smartphone_sales WHERE YEAR(purchase_time) = ‘2015’

Ack! The query bombed! What went wrong?

You used the YEAR function to query the YEAR information from the purchase_time column. You forgot to request the return value for YEAR(purchase_time). Instead, you just queried the value for purchase_time.

Oops! The database can’t process that request.

Here’s a fix for MySql version 5.7.6 or higher: Try using Generated Columns. That should do the trick.

Here’s another fix: You can experiment with other query strategies that don’t need function calls, like using a 2-way range condition like this:

SELECT count(*) FROM global_smartphone_sales WHERE purchase_time >= ‘2015-01-01’ AND purchase_time < ‘2016-01-01’

Now you’ve got the number! Approximately 1.4 billion smartphone sales happened in 2015.

12) Make Sure It’s Not a Problem With an OR Condition:

Now let’s say the Woz wants to know the total number of iPhones sold after 2015. You write a query to pull the data from the global_smartphone_sales database. You mistakenly use an OR condition like this:

SELECT count(*) FROM global_smartphone_sales WHERE phone = ‘iphone’ OR purchase_time >= ‘2016-01-01’

You thought the OR condition was perfect because it queries both the “global_smartphone_sales” and “purchase_time” columns. But the database won’t respond the right way. The OR condition causes the database to grab the results from both sides separately and it screws up the result.

Here’s a fix: Don’t use an OR condition. Instead, split both sides of the condition into separate queries. Then bring them together with a UNION clause like this:

SELECT count(*) FROM global_smartphone_sales WHERE phone = ‘iphone’

UNION

SELECT count(*) FROM global_smartphone_sales WHERE purchase_time >= ‘2016-1-1’

The separate queries let the database grab the numbers, and the UNION clause combines the results.

SQL Status Update #3: Thanks for reading this far! That’s a lot of growth on the SQL knowledge meter. You’re that much closer to becoming a mythic-level SQL coder. You reached SQL Status Level 60: EPIC.

13) Don’t Write a Query for a Multi-Ordered Sort on an Old Version of MySQL:

The Woz wants more, and this time it’s complicated. He wants a list of all iPhone sales that sorts by the type of iPhone in ascending order, and then it will sort by the date of the sale in descending order.

You take a sip of coffee, close your eyes, and guess. You choose a query that pulls information from the global_iphone_sales database. You end up sorting by ASC and DESC with the same ORDER BY clause:

SELECT phone_type, purchase_time FROM global_iphone_sales ORDER BY phone_type ASC, purchase_time DESC

Foiled again! Only MySQL 8 and up can do a multi-ordered sort like this. If you have an older version, you won’t be able to sort with ASC and DESC through a single ORDER BY clause.

Tell Woz to upgrade or think of a work-around.

Here’s the work-around: You can use Generated Columns in this case too. Make a reversed column and use it for sorting. The generated column should have a negative numeric value. It will track back to the number on the original column, but it will sort in the preferred order on the generated column.

Everything is still sorted the same way on the original ORDER BY clause. However, on the Generated Column, you’ll get the results you want.

14) Make Sure the Conditions Match the Column Type:

Woz is back. He wants to know how many iPhones Apple sold in West Virginia. You know that the states are numerically coded in the database and West Virginia is signified by “9.” You write the query like this:

SELECT count(*) FROM global_iphone_sales WHERE state = 9

Errrr! Wrong again! What happened?

This is an easy mistake to make, and it’s just as easy to fix. The column type for “state” is VARCHAR, not numeric. So querying by a number won’t work.

Here’s the fix: You have to treat the number as a VARCHAR text label, not as a number. You have to put the 9 in single quotes like this: ‘9’. Let’s write it again:

SELECT count(*) FROM global_iphone_sales WHERE state = ‘9’

One more idea: You could also change the column type to INT instead of VARCHAR, since the column just contains numbers. However, before you start changing column types to optimize a single query, be careful. Make sure you’re not going to mess up any other queries as a result. In other words, you better know what you’re doing!

15) Don’t Use the Wildcard Operator “%” for LIKE Queries:

The Woz has a theory. He thinks people with the characters “bob” in their names are more likely to buy iPhones. He wants the stats.

You need to query the global_iphone_sales database to bring up all the sales with names like Bob, Spongebob, Bobby, Bobby-Joe, Billy-Bob, etc. You decide to use a LIKE query with the prefix wildcard operator “%” for multiple characters:

SELECT * FROM global_iphone_sales WHERE buyer LIKE ‘%bob%’

Houston, we have a problem! It’s the slowest search query you’ve ever made!

This StackOverflow writer says that LIKE queries are only good if you’re doing them against:

A column (not a result of a function)
The start of a column (for example, LIKE 'bob%' instead of LIKE '%blah%')
An indexed column.

Can you convince the Woz to dump this ‘bob’ idea? No?

Here’s the fix: Instead of a LIKE query, try a FULLTEXT index with MATCH() AGAINST().

SQL Status Update #4: We're really impressed! Your SQL status is *almost* as good as it gets! Time for a promotion? You've reached SQL Status Level 80: LEGENDARY.

Use SQLBot to Boost Efficiency Even More

The above tips will optimize your SQL queries, but SQLBot will boost your efficiency even more. SQLBot bypasses one of the biggest SQL slowdowns of all: Procrastination!

Here’s the issue: Maybe we’re afraid of seeing bad results. Maybe we want to keep the team positive. So we delay querying the database and drag our feet when sharing the stats.

SQLBot eliminates this tendency to procrastinate. It automatically creates reports from your SQL queries (daily, weekly or according to your schedule). Then it sends those reports to your team by email or Slack. How cool is that?

FINAL SQL STATUS: Congratulations! We’re so proud you read the whole article. When you sign up for SQLBot, your office mates will sing songs about your mythic abilities. You've reached SQL Status Level 100: MYTHIC!