MySQL Bugs: #100597: INDEX hint does not affect count(*) execution

Bug #100597	INDEX hint does not affect count(*) execution
Submitted:	21 Aug 2020 7:10	Modified:	24 Aug 2020 12:24
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If we try to use hint to get count(*) to an index, the index is not used even if EXPLAIN shows that it will.

On a newly started server, we observe that no page from the world.city table is present in the buffer pool:

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;
Empty set (4.94 sec)

We try to use a hint to make MySQL use a specific index when counting number of rows:

mysql> explain select count(*) from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `world`.`city`
mysql>  select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

However, when we check the buffer pool, we see that the PRIMARY index was read:

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;
+------------+-----------+------------+------------+
| index_name | buf_count | leaf_pages | percent    |
+------------+-----------+------------+------------+
| PRIMARY    |        25 |         24 | 104.166667 |
+------------+-----------+------------+------------+
1 row in set (4.81 sec)

If we add a WHERE condition, we see that the index is used as excepted:

mysql> explain select count(*) from city where countrycode < 'ZZZ';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL | 4079 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `world`.`city` where (`world`.`city`.`CountryCode` < 'ZZZ')
mysql>  select count(*) from city where countrycode < 'ZZZ';
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;
+-------------+-----------+------------+------------+
| index_name  | buf_count | leaf_pages | percent    |
+-------------+-----------+------------+------------+
| PRIMARY     |        25 |         24 | 104.166667 |
| CountryCode |         8 |          7 | 114.285714 |
+-------------+-----------+------------+------------+
2 rows in set (4.90 sec)

Note that even when a hint is not used, EXPLAIN is misleading:

mysql> explain select count(*) from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select count(0) AS `count(*)` from `world`.`city`

How to repeat:
Start a new server (I set innodb_buffer_pool_load_at_startup=OFF in config file to make sure the table pages are not loaded)

use world;

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;

explain select count(*) from city;
select count(*) from city;

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;

explain select count(*) from city where countrycode < 'ZZZ';
select count(*) from city where countrycode < 'ZZZ';

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;

Suggested fix:
Use index for counting when hint are used, or at least fix EXPLAIN so it shows that there is no plan to use the index.

I am sorry.  I made a cut&paste error, so it did not show the query with hint.  Here is what I wanted to show:

mysql> EXPLAIN  select /*+ INDEX(city CountryCode) */ count(*) from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select /*+ INDEX(`city`@`select#1` `CountryCode`) */ count(0) AS `count(*)` from `world`.`city`
mysql> select /*+ INDEX(city CountryCode) */ count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.01 sec)

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;
+------------+-----------+------------+------------+
| index_name | buf_count | leaf_pages | percent    |
+------------+-----------+------------+------------+
| PRIMARY    |        25 |         24 | 104.166667 |
+------------+-----------+------------+------------+
1 row in set (4.97 sec)

Hi Oystein,

Thank you for your bug report.

However, I do not think that I have got the same results as you. The only difference between your and my `world` schema is that `city` table has a column named 'Country`, but not `CountryCode`, although data are identical.

I have first run your queries from the How-to-repeat section and then the additional queries with a hint.

Here are my results:

==========================================

d	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	city	NULL	index	NULL	Population	4	NULL	4079	100.00	Using index
count(*)
4079
index_name	buf_count	leaf_pages	percent
PRIMARY	20	19	105.263158
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	city	NULL	index	Country	Country	12	NULL	4079	100.00	Using where; Using index
count(*)
4079
index_name	buf_count	leaf_pages	percent
PRIMARY	20	19	105.263158
Country	7	6	116.666667
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	city	NULL	index	NULL	Population	4	NULL	4079	100.00	Using index
count(*)
4079
index_name	buf_count	leaf_pages	percent
PRIMARY	20	19	105.263158
Country	7	6	116.666667

Hi!  I am sorry I messed things up a bit.  However, regarding the table definition, I downloaded the database from https://dev.mysql.com/doc/index-other.html, and the definition for city is:

mysql> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

The correct "How to repeat" should be:

Start a new server (I set innodb_buffer_pool_load_at_startup=OFF in config file to make sure the table pages are not loaded)

use world;

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;

explain select /*+ INDEX(city CountryCode) */ count(*) from city;
select /*+ INDEX(city CountryCode) */ count(*) from city;

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;

explain select count(*) from city where countrycode < 'ZZZ';
select count(*) from city where countrycode < 'ZZZ';

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 '%city%' group by index_name) buf_stats using(index_name) where index_stats.table_name like 'city' and stat_name = 'n_leaf_pages' ;

That said, your results still illustrates part of the issue.  EXPLAIN says it will use an index (Population in your case), but buffer pool contents shows that this index has not been used.  My extra point is that even if a use a hint, the index will not be used.

Hi Øystein ,

I have been using that innodb-...... option regularly, for the cases like this one.

You are right about my previous results showing the problem again. With changed `world` schema, I am able to repeat fully the behaviour.

Also, I agree that this is a low priority bug.

Verified as reported.