EmergencyEMERGENCY? Get 24/7 Help Now!

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

 | December 14, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, JSON, MySQL, MySQL 101

PREVIOUS POST
NEXT POST

used_columns covered index

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.

 

PREVIOUS POST
NEXT POST
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.

2 Comments

  • “used_columns”: [
    “ID”,
    “Name”,
    “CountryCode”,
    “District”
    ],

    alter table City add index cov(CountryCode, District, Name);

    Is there any dependency of order of columns while adding a covered index with used_columns output ?

  • I think columns are listed in “used_columns” member in just alphabetical order, therefore you still need check your where clauses (check query to which original was converted in the output, produced by SHOW WARNINGS too) and follow rule what fields should be in same order as they are queried (INDEX(a,b) would work for WHERE a=X or b=Y, but would not for WHERE b=Y or a=X)

Leave a Reply