I'm taking care of an elderly 5.5.41 Mysql database and I was taking a look at the indexes with the following query:

select i.table_name, i.index_name, i.rows_per_key,
    coalesce(t.rows_read, 0) as tbl_rows_read,
    coalesce(s.rows_read, 0) as idx_rows_read
from information_schema.innodb_index_stats i
left join information_schema.table_statistics t using (table_schema, table_name)
left join information_schema.index_statistics s using (table_schema, table_name, index_name)
where i.table_schema = 'my_schema'
order by i.table_name, coalesce(s.rows_read, 0) desc, i.index_name;

For the rows_per_key column, some of the tables return values like 9234487, 9234487, 4273, 2, 0, 0. I take this to mean that once a query specifies the first four columns in a SELECT, whether or not the fifth and sixth columns of the index are specified, the index selectivity does not improve.

The only time it could be useful is if only the fields in the index are sufficient to answer the query, then the planner does not need to consider the table at all.

But apart from that, if I redefine the index to contain only first for fields, I won't lose in performance?

By extension, since the index is then held in fewer pages, it is even more likely to be buffered in its entirety.

  • Would you care to show us the query and the index it is using? – Rick James Oct 13 '17 at 20:19
  • Obviously queries are using the index because rows_read is moving. But this question is less about the queries and more about selectivity. I'll see if I can dig some queries out of the slow logs that use the first four columns versus all six, but this is exactly what I was hoping to avoid in the first place. – dland Oct 16 '17 at 7:44
  • Run SHOW INDEXES FROM my_table; -- see if you get the same info, but in a different format. – Rick James May 26 at 19:13

Comparing these:

  • Each can handle the same queries (assuming that e is not in the query).
  • If exactly those 5 columns exist in the query, then the longer one has an advantage of being "covering". This might give you a 2x speedup.
  • Yes the longer one takes more disk space. This impacts the speed of it only slightly. (Obviously, the impact depends on the size of e.)
  • There is no hard-and-fast rule saying you should stop with 4 versus have all 5.
  • My Rule of Thumb says "don't go past 5".

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.