Menu

Accelerate time to resolution

SQL Performance Tuning Tutorial – MySQL Query Optimization Tips

September 11, 2018
Tomer Shay @ EverSQL

This is the first part of our SQL Performance Tuning series. In this article, we’ll focus and MySQL related examples, but the same concepts can be applied to many other relational databases.

Now more than ever, software engineers need to have vast knowledge in SQL performance tuning.
The shift is happening in both small startups and large enterprises. Nowadays, developers are the ones writing the SQL queries and database access layer.

Your friends are probably optimizing SQL queries automatically – just submit your query 

SELECT
        prod.* 
    FROM
        products prod 
    LEFT JOIN
        purchase_history purch 
            ON prod.id = purch.product_id
     WHERE
        prod.name LIKE '%pizza%' 
        AND DATE(purch.purchase_date) > '2018-08-30' 
    GROUP BY
        prod.id

Start optimizing!

As technology advances, even the most novice end-users are becoming impatient and will expect your application to work quickly, even quicker than you’d expect. Therefore, we, as software developers, are bound to meet that endless need for fast and immediate response time, anywhere and anytime.

It doesn’t really matter if you’re using a database abstraction layer (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, or others) or writing native SQL queries, you’ll eventually be challenged with tuning the queries you’re sending to your database.

Create indexes, but do it wisely

Some will say that indexing is the most important part of SQL query tuning. In many cases, it can definitely be true. First, get familiar with the aspects you should consider when choosing the optimal indexes.

Remember, when index, you should pay close attention to the query’s WHERE clause and table JOINs, as those statements include the critical filtering parts of the query.

Also, major bottlenecks in data search can be the GROUP BY and ORDER BY parts. Said that, a potential hiccup will be that you may not be able to index them in some cases, as we explained here. Therefore, you might need to re-think the design of your query before creating the indexes, to make sure you write great queries, but also write index-able queries.

Once you got indexing figured out for one query, don’t stop there. Widen your view and look into other important queries in your application. The more queries you look at, the more you’ll think about the best indexes to create. Make sure you combine indexes whenever possible, and remove indexes which aren’t needed anymore. Looking at the entire application’s scope will always be better than looking at a single query’s scope.

That said, having more indexes than you need can also backfire on you, as they can slow down write operations (such as INSERT / UPDATE statements). So create indexes to optimize your SQL queries, but do it wisely.

0 Comment

Leave a Reply