2

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.

3
  • Would you care to show us the query and the index it is using?
    – Rick James
    Oct 13, 2017 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, 2017 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, 2019 at 19:13

1 Answer 1

0

Comparing these:

INDEX(a,b,c,d,e)
INDEX(a,b,c,d)
  • 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 and acknowledge you have read our privacy policy.

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