| Bug #78943 | EXPLAIN FORMAT="JSON" ignores FORCE INDEX for some output parts | ||
|---|---|---|---|
| Submitted: | 23 Oct 2015 13:52 | Modified: | 26 Oct 2015 7:19 |
| Reporter: | Jan Kahoun | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.24-72.2-log, 5.6.27 | OS: | Ubuntu (14.04) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | explain, Optimizer, used_key_parts | ||
[24 Oct 2015 7:54]
MySQL Verification Team
Hello Jan Kahoun, Thank you for the report. Thanks, Umesh
[24 Oct 2015 7:54]
MySQL Verification Team
// 5.6.27
[root@cluster-repo ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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
Database changed
mysql> create table test (
-> `ai` int unsigned NOT NULL ,
-> `id` int unsigned NOT NULL,
-> PRIMARY KEY (`ai`, `id`),
-> KEY `ai` (`ai`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into test values (1,1), (2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> explain format="json"
-> select *
-> from test FORCE INDEX(ai)
-> where ai = 2 and id = 2\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"table_name": "test",
"access_type": "const",
"possible_keys": [
"ai"
],
"key": "ai",
"used_key_parts": [
"ai",
"id"
],
"key_length": "8",
"ref": [
"const",
"const"
],
"rows": 1,
"filtered": 100,
"using_index": true
}
}
}
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test FORCE INDEX(ai) where ai = 2 and id = 2;
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE | test | const | ai | ai | 8 | const,const | 1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> alter table test drop primary key;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> explain format="json" select * from test FORCE INDEX(ai) where ai = 2 and id = 2\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"table_name": "test",
"access_type": "ref",
"possible_keys": [
"ai"
],
"key": "ai",
"used_key_parts": [
"ai"
],
"key_length": "4",
"ref": [
"const"
],
"rows": 1,
"filtered": 100,
"attached_condition": "(`test`.`test`.`id` = 2)"
}
}
}
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test FORCE INDEX(ai) where ai = 2 and id = 2;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | ai | ai | 4 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
[24 Oct 2015 12:06]
Øystein Grøvlen
Hi, this behavior is as expected. In InnoDB, the primary key is included in secondary indexes to identify the actual rows. From MySQL 5.6 on, conditions on the primary key will be evaluated on the index to avoid look-ups into the base table. It is possible to turn off this behavior by setting optimizer_switch= "use_index_extensions=off", but that will normally lead to reduce performance.
[26 Oct 2015 7:19]
Jan Kahoun
Ok, it make sense for me. Maybe the output should be slighlty different to avoid confusion.
Description: When you execute EXPLAIN FORMAT="JSON" on query with FORCE INDEX, you can see wrong info. In first EXPLAIN output below you can see, that it shows that used key is "ai". This is ok, but it also shows that two parts are used even if the forced index is only on single column. Also "key_length" and "ref" parts are wrong. Looks like the info is taken from PK? When the PK is removed, the info is ok. EXPLAIN output with wrong info: { "query_block": { "select_id": 1, "table": { "table_name": "test", "access_type": "const", "possible_keys": [ "ai" ], "key": "ai", "used_key_parts": [ "ai", "id" ], "key_length": "8", "ref": [ "const", "const" ], "rows": 1, "filtered": 100, "using_index": true } } } EXPLAIN output with proper info: { "query_block": { "select_id": 1, "table": { "table_name": "test", "access_type": "ref", "possible_keys": [ "ai" ], "key": "ai", "used_key_parts": [ "ai" ], "key_length": "4", "ref": [ "const" ], "rows": 1, "filtered": 100, "attached_condition": "(`temp`.`test`.`id` = 2)" } } } How to repeat: create table test ( `ai` int unsigned NOT NULL , `id` int unsigned NOT NULL, PRIMARY KEY (`ai`, `id`), KEY `ai` (`ai`) ) ENGINE=InnoDB; insert into test values (1,1), (2,2); # wrong output, it shows info from PK explain format="json" select * from test FORCE INDEX(ai) where ai = 2 and id = 2; alter table test drop primary key; # proper output explain format="json" select * from test FORCE INDEX(ai) where ai = 2 and id = 2; Suggested fix: For queries with FORCE INDEX show proper info in parts "used_key_parts", "key_length" and "ref" for forced index.