Bug #104501 select count not using secondary index
Submitted: 2 Aug 2021 6:53 Modified: 2 Aug 2021 13:09
Reporter: Yue Mao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0.23 OS:CentOS (8)
Assigned to: CPU Architecture:x86 (Intel(R) Xeon(R) CPU E5-26xx v4)
Tags: count, INDEX

[2 Aug 2021 6:53] Yue Mao
Description:
mysql>  select count(1) from history_price;
+----------+
| count(1) |
+----------+
| 22355452 |
+----------+
1 row in set (20.31 sec)

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

The Explain says this sql using index (kid), but not actually used. 

But use index when there is a WHERE clause:

mysql>  select count(1) from history_price where id > 0;
+----------+
| count(1) |
+----------+
| 22355452 |
+----------+
1 row in set (4.80 sec)

We got a lower exection time, from 20s to 4.8s. In addition, we can check the LRU table. Whitout WHERE clause:

mysql> select pool_id, lru_position,space, page_type,index_name from INNODB_BUFFER_PAGE_LRU limit 10;
+---------+--------------+-------+-----------+------------+
| pool_id | lru_position | space | page_type | index_name |
+---------+--------------+-------+-----------+------------+
|       0 |            0 |    10 | INDEX     | PRIMARY    |
|       0 |            1 |    10 | INDEX     | PRIMARY    |
|       0 |            2 |    10 | INDEX     | PRIMARY    |
|       0 |            3 |    10 | INDEX     | PRIMARY    |
|       0 |            4 |    10 | INDEX     | PRIMARY    |
|       0 |            5 |    10 | INDEX     | PRIMARY    |
|       0 |            6 |    10 | INDEX     | PRIMARY    |
|       0 |            7 |    10 | INDEX     | PRIMARY    |
|       0 |            8 |    10 | INDEX     | PRIMARY    |
|       0 |            9 |    10 | INDEX     | PRIMARY    |
+---------+--------------+-------+-----------+------------+

With WHERE clause:
mysql> select pool_id, lru_position,space, page_type,index_name from INNODB_BUFFER_PAGE_LRU limit 10;
+---------+--------------+-------+-----------+------------+
| pool_id | lru_position | space | page_type | index_name |
+---------+--------------+-------+-----------+------------+
|       0 |            0 |    10 | INDEX     | kid        |
|       0 |            1 |    10 | INDEX     | kid        |
|       0 |            2 |    10 | INDEX     | kid        |
|       0 |            3 |    10 | INDEX     | kid        |
|       0 |            4 |    10 | INDEX     | kid        |
|       0 |            5 |    10 | INDEX     | kid        |
|       0 |            6 |    10 | INDEX     | kid        |
|       0 |            7 |    10 | INDEX     | kid        |
|       0 |            8 |    10 | INDEX     | kid        |
|       0 |            9 |    10 | INDEX     | kid        |
+---------+--------------+-------+-----------+------------+

 

How to repeat:
CREATE TABLE `history_price` (
  `id` int NOT NULL AUTO_INCREMENT,
  `coin` varchar(16) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
  `ts` bigint NOT NULL,
  `open` decimal(18,8) NOT NULL,
  `close` decimal(18,8) NOT NULL,
  `low` decimal(18,8) NOT NULL,
  `high` decimal(18,8) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_coin_time_close` (`coin`,`create_time`,`close`),
  KEY `kid` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22355453 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 

mysql> show table status\G;
*************************** 1. row ***************************
           Name: history_price
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 21683550
 Avg_row_length: 85
    Data_length: 1843396608
Max_data_length: 0
   Index_length: 1164640256
      Data_free: 3145728
 Auto_increment: 22355453
    Create_time: 2021-08-02 03:16:57
    Update_time: 2021-06-19 21:15:28
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

Suggested fix:
using scan index to count rows.
[2 Aug 2021 11:18] Yue Mao
OPTIMIZER_TRACE shows this query is not using index.

select count(1) from history_price | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select count(1) AS `count(1)` from `history_price`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`history_price`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`history_price`",
                "table_scan": {
                  "rows": 22245300,
                  "cost": 109774
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`history_price`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 22245300,
                      "access_type": "scan",
                      "resulting_rows": 2.22e7,
                      "cost": 2.33e6,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 2.22e7,
                "cost_for_plan": 2.33e6,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`history_price`",
                  "attached": null
                }
              ]
            }
          },
          {
            "optimizing_distinct_group_by_order_by": {
            }
          },
          {
            "finalizing_table_conditions": [
            ]
          },
          {
            "refine_plan": [
              {
                "table": "`history_price`"
              }
            ]
          },
          {
            "considering_tmp_tables": [
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
[2 Aug 2021 13:09] MySQL Verification Team
Hi Mr. Mao,

Thank you very much for your bug report.

However , this is not a bug. Please, let us explain why it is not a bug.

InnoDB is a MVCC storage engine, which means that each transaction sees only changes within its scope. Hence a row count, that is visible to a transaction, can and does differ between sessions. This occurs in auto_commit mode as well, since it has in it inherent BEGIN / COMMIT statements. 

Therefore, the only way for a transaction to know EXACTLY how many rows are there is to count all rows. When you count all rows, you can't use indices, since sequential counting is much faster.

If you need an approximate number of rows, you can query I_S tables, as it is explained in our Reference Manual.

Not a bug.