used_columns: EXPLAIN FORMAT=JSON tells when you should use covered indexes

In the “MySQL Query tuning 101” video, Alexander Rubin provides an excellent example of when to use a covered index. On slide 25, he takes the query select name from City where CountryCode = ’USA’ and District = ’Alaska’ and population > 10000 and adds the index cov1(CountryCode, District, population, name) on table City. With Alex’s query tuning experience, making the right index decision is simple – but what about us mere mortals? If a query is more complicated, or simply uses more than one table, how do we know what to do? Maintaining another index can slow down INSERT statements, so you need to be very careful when choosing one. Examining the array “used_columns” could help out.

Let’s assume a more complicated version of the query was used in “MySQL Query tuning 101”:

Can we use a covered index here?

A traditional text-based EXPLAIN  already shows that it is a pretty good plan:

Can we make it better? Since our topic is covered indexes, let’s consider this possibility.

EXPLAIN FORMAT=JSON will tell us to which columns we should add covered index:

The answer is in the array “used_columns”. It lists the ID (primary key) and all columns which I used in the query:

Now we can try adding a covered index:

EXPLAIN  confirms what index access (“using_index”: true ) is used:

It also provides such metrics as:

• query_cost – 296.28 for the indexed table against 927.92 (smaller is better)
• rows_examined_per_scan – 2 versus 18 (smaller is better)
• filtered – 100 versus 10 (bigger is better)
• cost_info – read_cost  and prefix_cost  for the indexed table are smaller than when not indexed, which is better. However,  eval_cost  and  data_read_per_join  are bigger. But since we read nine times less rows overall, the cost is still better.

Conclusion: if the number of columns in used_columns  array is reasonably small, you can use it as a guide for creating a covered index.

Sveta Smirnova

Sveta joined Percona in 2015. Her main professional interests are problem solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as a Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of the book "MySQL Troubleshooting" and JSON UDF functions for MySQL.