MySQL Bugs: #81854: Force index is skipped while executing select count(*)

Bug #81854	Force index is skipped while executing select count(*)
Submitted:	15 Jun 2016 7:55	Modified:	23 Nov 2016 18:58
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After implementing WL#6742 in 5.7, MySQL always choose primary key to count row numbers on the table, even force index is specified. And this may lead to performance regression if the secondary index is much smaller than the cluster index

How to repeat:
A simple sbtest table with 2,000,000 records

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)

Restart the server to cleanup buffer pool....

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (3.88 sec)

Restart the server to cleanup buffer pool....

mysql> select count(*) from sbtest1 force index(k_1);
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (3.83 sec)

mysql> explain select count(*) from sbtest1 force index(k_1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

Restart the server to cleanup buffer pool....

// Add a condition so index k_1 will be used...

mysql> select count(*) from sbtest1 where k > 0;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (1.04 sec)

mysql> explain select count(*) from sbtest1 where k > 0;
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | k_1           | k_1  | 4       | NULL | 960984 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
Don't skip force-index for such scenario if user knows there is a faster way to count row numbers....

Hello Zhai,

Thank for the report and feedback!

Thanks,
Umesh

-- 5.7.13

[umshastr@hod03]~/bugs/sysbench: sysbench/sysbench --test=/home/umshastr/bugs/sysbench/sysbench/tests/db/parallel_prepare.lua  --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=2000000 --oltp_tables_count=1 --mysql-db=test --mysql-user=root --db-driver=mysql --mysql-socket=/tmp/mysql_ushastry.sock prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 2000000 records into 'sbtest1'

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.13: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.13-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
1 row in set (0.00 sec)

-- 5.6.31

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.31: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql>

-- 

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.49 sec)

mysql> select count(*) from sbtest1 force index(k_1);
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.49 sec)

mysql> explain select count(*) from sbtest1 force index(k_1);
+----+-------------+---------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | sbtest1 | index | NULL          | k_1  | 4       | NULL | 1921969 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1 where k > 0;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.62 sec)

mysql> explain select count(*) from sbtest1 where k > 0;
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest1 | range | k_1           | k_1  | 4       | NULL | 960984 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

Posted by developer:
 
Noted in 5.7.18, 8.0.1 changelogs.

FORCE INDEX was ineffective for SELECT COUNT(*) queries.