Advanced MySQL 8

Optimizing a query with the WHERE clause

Before we start looking at how to optimize a query with the WHERE clause, you need to know the optimization recipe that you have to apply to be efficient.

Here is an optimization recipe:

1. Identify the query that is slow
2. Run an EXPLAIN plan on this slow query
3. Identify what kind of slow problem your query is facing (for example, a full scan)
4. Run a SHOW CREATE TABLE on the tables that are part of the slow query
5. Identify whether the query uses the correct index and the order of the WHERE clause columns
6. Make the required corrections or modifications

Before turning to an example from the recipe, I'd like to explain how an index works in MySQL so that you can get an idea of the impact on your queries by ...