Efficiently querying your data: best practices

As data sets grow in size and complexity, it becomes essential to query that data efficiently. One of the most crucial ways to achieve this is by using indexes. Indexes help to optimize data retrieval, and while they are derived from your access patterns, it is possible to obfuscate them. So in this post, we will explore index obfuscation and how to avoid it.

Index obfuscation

Imagine you have a table full of movies, and you want to filter out movies that are under two hours long. The length of each movie is in minutes, so you need to divide the length column by 60 to convert it to hours before running your query. However, by doing this, you've obfuscated your column and made it difficult for MySQL to use your index.

To understand this better, let's look at how to avoid index obfuscation. First, always leave your column alone as much as possible! Any changes you make to it, such as dividing or combining it with other columns, make it more difficult to use an index effectively. Instead, move everything to the other side of the operator when possible.

In our case, instead of dividing the length column by 60, we should multiply it by 60 on the other side of the operator. By doing this, we allow the MySQL engine to calculate the result once and compare it against the index more efficiently.

For example, instead of this:

SELECT * FROM film WHERE length / 60 < 2;

We should write it like this:

SELECT * FROM film WHERE length < 2 * 60;

This simple change can lead to a significant improvement in query performance because it allows MySQL to potentially utilize an index on the length column.