Description:
If I create an additional covering index for a sysbench table, the in-memory estimate will be only 1 page (observed in debugger) even if entire index is in buffer. Because of this, another non-covering index is chosen instead.
After I have created the index, I check the information schema table for buffer content:
mysql> select index_name, buf_count, stat_value leaf_pages, (100.00*buf_count)/stat_value percent from mysql.innodb_index_stats index_stats join (select index_name, count(*) buf_count from information_schema.innodb_buffer_page where table_name like '%sbtest1%' group by index_name)buf_stats using(index_name) where index_stats.table_name like 'sbtest1' and stat_name = 'n_leaf_pages' ; +------------+-----------+------------+------------+
| index_name | buf_count | leaf_pages | percent |
+------------+-----------+------------+------------+
| PRIMARY | 137103 | 126667 | 108.238926 |
| k_1 | 8323 | 8313 | 100.120294 |
| k_cov | 121991 | 120500 | 101.237344 |
+------------+-----------+------------+------------+
3 rows in set (6.32 sec)
However, the new covering index is not selected for the query:
mysql> explain SELECT id, k, c, pad FROM sbtest1 WHERE k = 5009291; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sbtest1 | NULL | ref | k_1,k_cov | k_1 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Using the debugger, shows that when handler::info() is called, buf_stat_per_index_t::get returns 1 (page).
Restarting the server seems to fix the problem.
How to repeat:
0. Start a server with sufficiently large memory (innodb_buffer_pool_size=32G in my case)
1. Create database:
create database sbtest;
2. Create a sysbench table with this command:
sysbench lua/select_random_points.lua --tables=1 --table_size=10000000 --db-ps-mode=disable --rand-type=uniform --random_points=1 prepare
3. Create an additional index:
create index k_cov on sbtest1(k, c, pad);
4. Check that entire index is in memory:
select index_name, buf_count, stat_value leaf_pages, (100.00*buf_count)/stat_value percent from mysql.innodb_index_stats index_stats join (select index_name, count(*) buf_count from information_schema.innodb_buffer_page where table_name like '%sbtest1%' group by index_name)buf_stats using(index_name) where index_stats.table_name like 'sbtest1' and stat_name = 'n_leaf_pages' ;
5. Show that new index is not used even if it is covering:
explain SELECT id, k, c, pad FROM sbtest1 WHERE k = 5009291;
Suggested fix:
Fix buf_stat_per_index_t::get so it returns correct numbers after index creation.
