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

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.


Share this post

Comments (2)

  • jayaram pagoti

    “used_columns”: [

    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 ?

    December 15, 2015 at 12:28 pm
  • Sveta Smirnova

    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)

    December 15, 2015 at 1:08 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.