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;
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.