0

Table structure for config:

  • property (primary key)
  • value
  • description

Query:

SELECT property, value FROM config

If I put a covering index on (property, value) the optimizer still chooses the PRIMARY index, but the extra in EXPLAIN is NULL. If I tell the optimizer to use my covering index, the extra in EXPLAIN is USING INDEX.

What's really happening here? Why is the optimizer choosing the PRIMARY index over my covering index by default? Am I avoiding disk IO by telling the optimizer to use my covering index?

1

PRIMARY index in InnoDB is a tree where the primary key field (property) is a key and remaining columns are the value. In a secondary index the key are the index fields and the value is a primary key. In your case the secondary index has fields (property, value, and property). (property, value) is a key and property is a value. The secondary index is larger and redundant, so MySQL make a right decision to use the PRIMARY index.

Check out a presentation I gave on InnoDB indexes structure for more details. https://www.slideshare.net/akuzminsky/efficient-indexes-in-mysql

  • Many thanks, will check that out. I discovered that I can use (value) effectively as a covering index since it contains fields (value, property). What I'm still confused about is what exactly is buffered into memory for the PK: is it just property with a pointer to the full row on disk, or is the value column also buffered into memory (I'm excluding description because it is actually secretly a TEXT column). – Dan Jan 12 '18 at 2:45
  • 1
    I didn't ask what storage engine you use. I assumed InnoDB, but it could be MyISAM. Each of them use different caching approach. Here's a video from a meetup youtube.com/watch?v=ytW1KV_lIbQ – akuzminsky Jan 12 '18 at 3:45
  • 1
    InnoDB caches all pages it reads from the disk in the buffer pool. So, after SELECT property, value FROM config it will cache all primary index. description is a different story. if it's stored in-page it will be cached. If it's stored in external pages (which is a case for large (>7k) values) then only a beginning (768 or 20 bytes depending on whether it's COMPACT or DYNAMIC format) will be cached. – akuzminsky Jan 12 '18 at 5:17
  • 1
    @Dan - more on redundancy in indexes -- mysql.rjweb.org/doc.php/index_cookbook_mysql . That also explains some simple techniques for deciding what indexes to have. (This is better than simply throwing lots of indexes on a table -- most will never be used.) – Rick James Jan 12 '18 at 13:45
  • 1
    @akuzminsky - Oops. But still, that is not quite correct. If the index/table is bigger then the buffer_pool, the scan will read all of the blocks, but only some of the blocks will remain in the BP. – Rick James Jan 12 '18 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.