Bug #73603 | MySQL 5.6.20 Not Using Index | ||
---|---|---|---|
Submitted: | 15 Aug 2014 14:28 | Modified: | 14 Jan 2015 11:16 |
Reporter: | Matthew Roberts | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 5.6.20, 5.6.24 | OS: | Windows (Server 2012 R2) |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX |
[15 Aug 2014 14:28]
Matthew Roberts
[18 Aug 2014 10:55]
Matthew Roberts
May be related to bug #28404
[14 Jan 2015 11:16]
MySQL Verification Team
Hello Matthew Roberts, Thank you for the report and test case. Thanks, Umesh
[14 Jan 2015 11:17]
MySQL Verification Team
// mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.24 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.24-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS `Test`; Query OK, 1 row affected (0.00 sec) mysql> USE `Test`; Database changed mysql> mysql> CREATE TABLE `Customers` ( -> `CustomerID` INT UNSIGNED NOT NULL AUTO_INCREMENT, -> `CustomerName` VARCHAR(45) NOT NULL, -> PRIMARY KEY (`CustomerID`) -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Customers(CustomerName) VALUES ('David'), ('Peter'), ('Monty'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `Orders` ( -> `OrderID` INT UNSIGNED NOT NULL AUTO_INCREMENT, -> `CustomerID` INT UNSIGNED NOT NULL, -> `OrderReference` VARCHAR(45) DEFAULT NULL, -> PRIMARY KEY (`OrderID`), -> KEY `Orders_CustomerID` (`CustomerID`), -> CONSTRAINT `Orders_CustomerID` FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Orders (CustomerID, OrderReference) VALUES (1, 'Lots Of Wine'),(1, 'Party Hats'),(2, 'Chocolate Cake'),(2, 'Some Beer'),(3, 'Airplane'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> LIMIT 1; +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | Customers | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE Customers ADD UNIQUE INDEX `Customers_CustomerName_UNIQUE` (`CustomerName`); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | Test.Orders.CustomerID | 1 | NULL | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE Customers DROP INDEX `Customers_CustomerName_UNIQUE`; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> ORDER BY Orders.OrderID DESC -> LIMIT 1; +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort | | 1 | SIMPLE | Customers | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE Customers ADD UNIQUE INDEX `Customers_CustomerName_UNIQUE` (`CustomerName`); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> ORDER BY Orders.OrderID DESC -> LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | SIMPLE | Orders | index | NULL | PRIMARY | 4 | NULL | 1 | NULL | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | Test.Orders.CustomerID | 1 | NULL | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ 2 rows in set (0.00 sec)
[14 Jan 2015 11:17]
MySQL Verification Team
// 5.5.42 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.5.42 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.42-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux2.6 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS `Test`; Query OK, 1 row affected (0.00 sec) mysql> USE `Test`; Database changed mysql> mysql> CREATE TABLE `Customers` ( -> `CustomerID` INT UNSIGNED NOT NULL AUTO_INCREMENT, -> `CustomerName` VARCHAR(45) NOT NULL, -> PRIMARY KEY (`CustomerID`) -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO Customers(CustomerName) VALUES ('David'), ('Peter'), ('Monty'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> CREATE TABLE `Orders` ( -> `OrderID` INT UNSIGNED NOT NULL AUTO_INCREMENT, -> `CustomerID` INT UNSIGNED NOT NULL, -> `OrderReference` VARCHAR(45) DEFAULT NULL, -> PRIMARY KEY (`OrderID`), -> KEY `Orders_CustomerID` (`CustomerID`), -> CONSTRAINT `Orders_CustomerID` FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO Orders (CustomerID, OrderReference) VALUES (1, 'Lots Of Wine'),(1, 'Party Hats'),(2, 'Chocolate Cake'),(2, 'Some Beer'),(3, 'Airplane'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> /* Going to do something stupid ('Using where; Using join buffer (Block Nested Loop)') and a lot of rows examined. */ mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | Test.Orders.CustomerID | 1 | | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ 2 rows in set (0.00 sec) mysql> /* Add a unique index on the customer name (note has nothing to do with our CustomerID join query) */ mysql> ALTER TABLE Customers ADD UNIQUE INDEX `Customers_CustomerName_UNIQUE` (`CustomerName`); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> /* Much better, less rows but still not looking at just 1. */ mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | Test.Orders.CustomerID | 1 | | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ 2 rows in set (0.00 sec) mysql> /* Okay forget the index, what about ORDER BY? */ mysql> ALTER TABLE Customers DROP INDEX `Customers_CustomerName_UNIQUE`; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> ORDER BY Orders.OrderID DESC -> LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | SIMPLE | Orders | index | NULL | PRIMARY | 4 | NULL | 1 | | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | Test.Orders.CustomerID | 1 | | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ 2 rows in set (0.00 sec) mysql> /* Add a unique index on the customer name (note has nothing to do with our CustomerID join query) */ mysql> ALTER TABLE Customers ADD UNIQUE INDEX `Customers_CustomerName_UNIQUE` (`CustomerName`); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> ORDER BY Orders.OrderID DESC -> LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | SIMPLE | Orders | index | NULL | PRIMARY | 4 | NULL | 1 | | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | Test.Orders.CustomerID | 1 | | +----+-------------+-----------+--------+---------------+---------+---------+------------------------+------+-------+ 2 rows in set (0.00 sec) // 5.5.42 build commit: c94e83d41c31599b3db78832a36fed876f477fce date: 2015-01-02 10:18:04 +0530 build-date: 2015-01-04 12:02:30 +0100 short: c94e83d branch: mysql-5.5 MySQL source 5.5.42
[4 May 2015 13:17]
Øystein Grøvlen
Posted by developer: I do not think that the given reproduction test case represent the real problem. The reason the index is not used for the test case is that the Customers table is so small that the Optimizer considers that using Block Nested Loop is less expensive than indexed join. This will not be the case for larger tables. For example, if I insert 3 more rows into the Customers table, I get another plan: mysql> EXPLAIN -> SELECT OrderID, CustomerName, OrderReference -> FROM Orders -> LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID -> LIMIT 1; +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | Customers | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-----------+------+---------------+------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.00 sec) mysql> INSERT INTO Customers(CustomerName) VALUES ('Evgeny'), ('Roy'), ('Guilhem'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT OrderID, CustomerName, OrderReference FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID LIMIT 1; +----+-------------+-----------+--------+---------------+---------+---------+-------------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+-------------------------------+------+-------+ | 1 | SIMPLE | Orders | ALL | NULL | NULL | NULL | NULL | 5 | NULL | | 1 | SIMPLE | Customers | eq_ref | PRIMARY | PRIMARY | 4 | bug20358266.Orders.CustomerID | 1 | NULL | +----+-------------+-----------+--------+---------------+---------+---------+-------------------------------+------+-------+ 2 rows in set (0.00 sec) Hence, in order to investigate further, I think we need another a reproduction test case that shows this issue with a larger table.