Description:
The query is `SELECT count(*) FROM users` from a table with an index on `user(name)`.
Here're three pieces of information:
1) explain format=json select count(*) from users;
Shows that the index is being used.
2) select * from information_schema.optimizer_trace
Shows full table scan as the chosen plan.
3) show status like 'Handler%';
Shows 0 changes in read-handlers.
How the count(*) is known without reading anything?
From reading the previous bugs, it feels that optimizer trace (1) might be totally ignored, that's why index is used by (2).
Is that true? In that case, (1) and (2) do not contradict.
But, then, how `COUNT(*)` is calculated without reading anything, so that `SHOW STATUS` is empty?
The operation is very fast, but not instant, it does appear to examine some data, so it should show up in the STATUS changes.
How to repeat:
First, create the table
```
DROP TABLE IF EXISTS users;
CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
salary MEDIUMINT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at DATETIME,
index (name)
);
DROP TABLE IF EXISTS names;
CREATE TABLE names (
name varchar(300) not null
);
INSERT INTO names (name) VALUES
('Emma'), ('Liam'), ('Noah'), ('Olivia'), ('William'), ('James'), ('Isabella'), ('Oliver'), ('Sophia'),
('Benjamin'), ('Elijah'), ('Charlotte'), ('Lucas'), ('Mia'), ('Mason'), ('Amelia'), ('Ethan'), ('Harper'),
('Logan'), ('Ava'), ('Alexander'), ('Abigail'), ('Sebastian'), ('Emily'), ('Michael'), ('Evelyn'), ('Daniel'),
('Madison'), ('Matthew'), ('Avery'), ('Henry'), ('Sofia'), ('Jackson'), ('Ella'), ('Samuel'), ('Scarlett'),
('David'), ('Victoria'), ('Joseph'), ('Aria'), ('Carter'), ('Grace'), ('Owen'), ('Chloe'), ('Wyatt'),
('Penelope'), ('John'), ('Riley'), ('Jack'), ('Lily'), ('Luke'), ('Aubrey'), ('Jayden'), ('Zoey'), ('Dylan'),
('Lila'), ('Levi'), ('Ellie'), ('Gabriel'), ('Aaliyah'), ('Nicholas'), ('Addison'), ('Isaac'), ('Natalie'),
('Lincoln'), ('Luna'), ('Christopher'), ('Savannah'), ('Joshua'), ('Brooklyn'), ('Andrew'), ('Hazel'),
('Julian'), ('Aurora'), ('Grayson'), ('Audrey'), ('Leah'), ('Bella'), ('Mateo'), ('Claire'), ('Ryan'),
('Stella'), ('Jaxon'), ('Paisley'), ('Leo'), ('Skylar'), ('Jonathan'), ('Samantha'), ('Charles'), ('Nora'),
('Adam'), ('Eleanor'), ('Thomas'), ('Caroline'), ('Xavier'), ('Nova'), ('Eli'), ('Genesis'), ('Pete'), ('Ann');
-- insert 1000 x count(names) users
INSERT INTO users (name, salary, deleted_at)
WITH RECURSIVE seq AS (SELECT 1 as n UNION ALL SELECT 1 + n FROM seq LIMIT 1000000)
SELECT
-- random name
(SELECT name FROM names ORDER BY RAND() LIMIT 1),
-- random salary up to 1000
rand()*10000,
-- 80% null, 20% deleted_at = now + random within 30 days
IF(rand()>0.2, null, DATE_ADD(now(), INTERVAL rand()*30 DAY))
FROM seq;
SET OPTIMIZER_TRACE="enabled=on";
```
Then here's detailed output of each command:
1) explain format=json select count(*) from users;
```
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "100338.40"
},
"table": {
"table_name": "users",
"access_type": "index",
"key": "name",
"used_key_parts": [
"name"
],
"key_length": "258",
"rows_examined_per_scan": 997214,
"rows_produced_per_join": 997214,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "617.00",
"eval_cost": "99721.40",
"prefix_cost": "100338.40",
"data_read_per_join": "266M"
}
}
}
```
2) select * from information_schema.optimizer_trace
```
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `users`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"table": "`users`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"table": "`users`",
"table_scan": {
"rows": 997214,
"cost": 617
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`users`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 997214,
"access_type": "scan",
"resulting_rows": 997214,
"cost": 100338,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 997214,
"cost_for_plan": 100338,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`users`",
"attached": null
}
]
}
},
{
"optimizing_distinct_group_by_order_by": {
}
},
{
"finalizing_table_conditions": [
]
},
{
"refine_plan": [
{
"table": "`users`"
}
]
},
{
"considering_tmp_tables": [
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
```
3)
```
FLUSH STATUS;
select count(*) from users;
show status like 'handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
```
Description: The query is `SELECT count(*) FROM users` from a table with an index on `user(name)`. Here're three pieces of information: 1) explain format=json select count(*) from users; Shows that the index is being used. 2) select * from information_schema.optimizer_trace Shows full table scan as the chosen plan. 3) show status like 'Handler%'; Shows 0 changes in read-handlers. How the count(*) is known without reading anything? From reading the previous bugs, it feels that optimizer trace (1) might be totally ignored, that's why index is used by (2). Is that true? In that case, (1) and (2) do not contradict. But, then, how `COUNT(*)` is calculated without reading anything, so that `SHOW STATUS` is empty? The operation is very fast, but not instant, it does appear to examine some data, so it should show up in the STATUS changes. How to repeat: First, create the table ``` DROP TABLE IF EXISTS users; CREATE TABLE users( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, salary MEDIUMINT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME, index (name) ); DROP TABLE IF EXISTS names; CREATE TABLE names ( name varchar(300) not null ); INSERT INTO names (name) VALUES ('Emma'), ('Liam'), ('Noah'), ('Olivia'), ('William'), ('James'), ('Isabella'), ('Oliver'), ('Sophia'), ('Benjamin'), ('Elijah'), ('Charlotte'), ('Lucas'), ('Mia'), ('Mason'), ('Amelia'), ('Ethan'), ('Harper'), ('Logan'), ('Ava'), ('Alexander'), ('Abigail'), ('Sebastian'), ('Emily'), ('Michael'), ('Evelyn'), ('Daniel'), ('Madison'), ('Matthew'), ('Avery'), ('Henry'), ('Sofia'), ('Jackson'), ('Ella'), ('Samuel'), ('Scarlett'), ('David'), ('Victoria'), ('Joseph'), ('Aria'), ('Carter'), ('Grace'), ('Owen'), ('Chloe'), ('Wyatt'), ('Penelope'), ('John'), ('Riley'), ('Jack'), ('Lily'), ('Luke'), ('Aubrey'), ('Jayden'), ('Zoey'), ('Dylan'), ('Lila'), ('Levi'), ('Ellie'), ('Gabriel'), ('Aaliyah'), ('Nicholas'), ('Addison'), ('Isaac'), ('Natalie'), ('Lincoln'), ('Luna'), ('Christopher'), ('Savannah'), ('Joshua'), ('Brooklyn'), ('Andrew'), ('Hazel'), ('Julian'), ('Aurora'), ('Grayson'), ('Audrey'), ('Leah'), ('Bella'), ('Mateo'), ('Claire'), ('Ryan'), ('Stella'), ('Jaxon'), ('Paisley'), ('Leo'), ('Skylar'), ('Jonathan'), ('Samantha'), ('Charles'), ('Nora'), ('Adam'), ('Eleanor'), ('Thomas'), ('Caroline'), ('Xavier'), ('Nova'), ('Eli'), ('Genesis'), ('Pete'), ('Ann'); -- insert 1000 x count(names) users INSERT INTO users (name, salary, deleted_at) WITH RECURSIVE seq AS (SELECT 1 as n UNION ALL SELECT 1 + n FROM seq LIMIT 1000000) SELECT -- random name (SELECT name FROM names ORDER BY RAND() LIMIT 1), -- random salary up to 1000 rand()*10000, -- 80% null, 20% deleted_at = now + random within 30 days IF(rand()>0.2, null, DATE_ADD(now(), INTERVAL rand()*30 DAY)) FROM seq; SET OPTIMIZER_TRACE="enabled=on"; ``` Then here's detailed output of each command: 1) explain format=json select count(*) from users; ``` { "query_block": { "select_id": 1, "cost_info": { "query_cost": "100338.40" }, "table": { "table_name": "users", "access_type": "index", "key": "name", "used_key_parts": [ "name" ], "key_length": "258", "rows_examined_per_scan": 997214, "rows_produced_per_join": 997214, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "617.00", "eval_cost": "99721.40", "prefix_cost": "100338.40", "data_read_per_join": "266M" } } } ``` 2) select * from information_schema.optimizer_trace ``` { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `users`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`users`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "`users`", "table_scan": { "rows": 997214, "cost": 617 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`users`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 997214, "access_type": "scan", "resulting_rows": 997214, "cost": 100338, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 997214, "cost_for_plan": 100338, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`users`", "attached": null } ] } }, { "optimizing_distinct_group_by_order_by": { } }, { "finalizing_table_conditions": [ ] }, { "refine_plan": [ { "table": "`users`" } ] }, { "considering_tmp_tables": [ ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] } ``` 3) ``` FLUSH STATUS; select count(*) from users; show status like 'handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 18 rows in set (0.00 sec) ```