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