Performance tune the hardware.

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.

Do not stand in the way of indexes

We’re being approached a lot by customers who’re asking us “why the database doesn’t use my index?”. Well, that’s a great question, with endless possible answers. But, in this article, we’ll try to provide several common options we see a lot, so hopefully, you’ll find them useful for your own use case.

Example #1 – Avoid wrapping indexed columns with functions

Consider this query, which counts the number of hot dogs purchased in the US on 2018. Just in case you’re curious, 18,000,000,000 hot dogs were sold in the US in 2018.

SELECT count(*) FROM us_hotdog_purchases WHERE YEAR(purchase_time) = ‘2018’

As you can see, we are using the YEAR function to grab the year part from the purchase_time column. This function call will prevent the database from being able to use an index for the purchase_time column search, because we indexed the value of purchase_time, but not the return value of YEAR(purchase_time).

To overcome this challenge and tune this SQL query, you can index the function’s result, by using  Generated Columns, which are available starting MySQL 5.7.5.

Another solution can be to find an alternative way to write the same query, without using the function call. In this example, we can transform that condition to a 2-way range condition, which will return the same results:

0 Comment

Leave a Reply