MySQL for Developers
3.5
Redundant and approximate conditions
3.7

Unlocking indexes with redundant and approximate conditions in MySQL

When it comes to database optimization, finding ways to improve query performance is crucial. One approach to unlocking indexes without making changes to the table structure is to use redundant and approximate conditions. Although not a common occurrence, these types of conditions have the potential to make a significant impact on performance once identified. In this video, we'll explore what redundant and approximate conditions are, why they're valuable, and how to use them to improve query performance.

Understanding redundant conditions

Redundant conditions refer to query conditions that logically cannot change the result set. Even though they don't produce any changes in the result, they're still valuable because they can help optimize the query. For instance, consider the following example of querying a people table:

SELECT * FROM people WHERE id <= 5

This query returns the first five rows from the people table based on the given condition. However, if we modify the query as shown below, we still get the same result.

SELECT * FROM people
  WHERE
    id <= 5
    AND
    id <= 10

The second condition is redundant because it doesn't change the result of the query at all. The key benefit of redundant conditions lies in the fact that they help unlock indexes without any changes to the table. So, it's worth looking out for these types of conditions when optimizing your database. In this example we're not getting any benefit from the redundant condition, but there are times when we can get huge benefits.

Redundant condition example

Consider a scenario where you have a to-do list and want to find all the to-do's that are due in the next 24 hours. You may have an index on the due date, but not the due date combined with due time. In this case, you can make use of the redundant condition, which logically cannot change the results and can help you use the available index more efficiently.

Here's the query without the redundant condition:

SELECT * FROM todos
  WHERE
  ADDTIME(due_date, due_time) BETWEEN NOW() AND NOW() + INTERVAL 1 DAY

The above query retrieves all the to-do's that are due within the next 24 hours based on the due_date and due_time columns. Unfortunately we've obfuscated the index on the due_date column.

In this case we can add a redundant condition that is broader than our original condition, but is not index-obfuscated.

SELECT * FROM todos
  WHERE
  ADDTIME(due_date, due_time) BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
  AND
  due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 DAY

Using the approximate condition helps to narrow down the result set even though it may include a few false positives. After narrowing down the result set, you can then use the expensive filtering, which is not indexable, to remove the false positives and get the correct result set.

Conclusion

Redundant and approximate conditions offer a powerful tool for optimizing database queries. Even if they aren't terribly common to find in the wild, they are fun to discover because they require no changes to the table structure to unlock the use of indexes.

About this lesson

Despite your best efforts, it may be impossible to index a condition correctly. In that case, you might be able to use a redundant condition to help narrow down records quickly.

06:23
Closed captioned

Meet your instructor, Aaron Francis

Developer Educator at PlanetScale

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.