Table structure for table config:
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?
InnoDB indexes are not automatically stored in the buffer pool. They're stored on disk. Both index pages and data pages are stored on disk.
Both index pages and data pages may be copied into the buffer pool in memory, depending on whether earlier queries had requested them. But this is not guaranteed.
Actually when I say "data pages" that's really the clustered index, i.e. PRIMARY. InnoDB stores everything as an index. In the case of the PRIMARY/clustered index, each entry includes all the other columns. That effectively makes the PRIMARY index the "data pages." In some databases, they use the term "index-organized table."
When the optimizer chooses your PRIMARY index, it goes without saying that a primary key read will be able to get all the other columns without any further lookups (except for blob/text data that extends over extra pages).
The "Using index" note in the EXPLAIN report only appears when the query reads all the columns it needs from the index, and the index is a secondary index (not the PRIMARY).
"Using index" has nothing to do with in-memory vs. read-from-disk. When a page is requested, it'll read from memory if it's in the buffer pool. If it's not in the buffer pool, it'll be copied from disk into the buffer pool, whether it's a PRIMARY or secondary index.
In fact, when the optimizer reports "Using index" it has no knowledge whether all, some, or none of the pages for the respective index are in the buffer pool, or have yet to be loaded from disk. It only knows that it can get all the columns it needs from one secondary index, without reading the clustered index.
Re your comment:
Yes, the entire row is in memory, not just the PK.
The buffer pool contains pages, exactly like what's on disk. The pages contain two or more rows of data, meaning the PK plus the columns associated with that PK. When the page gets read from disk, a copy of it is made in the buffer pool. There it stays, byte-for-byte a clone of what's on disk.
Queries only read rows stored in pages in the buffer pool. If a requested row isn't yet in memory, the page that holds that row will immediately be read from disk into the buffer pool, then the query proceeds to read it from memory.
Pages may be evicted from the buffer pool if you need other pages from disk and the buffer pool is full. Thus the buffer pool may be much smaller than the total database on disk. Over time, the most-frequently used pages tend to stay in the buffer pool.