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:
None 
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
Description:
Hi,

Noticed the optimiser being a bit strange with its choice of indexes.

If I add an index on a column that the join is not on, it then remembers the PRIMARY index (not the one I've added) but without it it's often doing table scans that it need not do.

I have found FORCEing the index achieves the desired results, so it seems to be just the optimiser being a bit strange.

Below is a simple table and the experiments that should show the strange behaviour.

On our production system where `Customers` is 18068227 rows it claims to look at them all instead of the limit (1000) until the index is added and takes a long time to return. With the index on the other column added the query completes instantly.

How to repeat:
CREATE DATABASE IF NOT EXISTS `Test`;
USE `Test`;

CREATE TABLE `Customers` (
 `CustomerID`   INT         UNSIGNED NOT NULL AUTO_INCREMENT,
 `CustomerName` VARCHAR(45)          NOT NULL,
 PRIMARY KEY (`CustomerID`)
);

INSERT INTO Customers(CustomerName) VALUES ('David'), ('Peter'), ('Monty');

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
);

INSERT INTO Orders (CustomerID, OrderReference) VALUES (1, 'Lots Of Wine'),(1, 'Party Hats'),(2, 'Chocolate Cake'),(2, 'Some Beer'),(3, 'Airplane');

/* Going to do something stupid ('Using where; Using join buffer (Block Nested Loop)') and a lot of rows examined. */
EXPLAIN
SELECT OrderID, CustomerName, OrderReference
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
LIMIT 1;

/* Add a unique index on the customer name (note has nothing to do with our CustomerID join query) */
ALTER TABLE Customers ADD UNIQUE INDEX `Customers_CustomerName_UNIQUE` (`CustomerName`);

/* Much better, less rows but still not looking at just 1. */
EXPLAIN
SELECT OrderID, CustomerName, OrderReference
FROM Orders 
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
LIMIT 1;

/* Okay forget the index, what about ORDER BY? */
ALTER TABLE Customers DROP INDEX `Customers_CustomerName_UNIQUE`;

/* All 5 rows looked at */
EXPLAIN
SELECT OrderID, CustomerName, OrderReference
FROM Orders 
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
ORDER BY Orders.OrderID DESC
LIMIT 1;

/* Add a unique index on the customer name (note has nothing to do with our CustomerID join query) */
ALTER TABLE Customers ADD UNIQUE INDEX `Customers_CustomerName_UNIQUE` (`CustomerName`);

/* Only 1 row looked at :-). Blazing fast */
EXPLAIN
SELECT OrderID, CustomerName, OrderReference
FROM Orders 
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
ORDER BY Orders.OrderID DESC
LIMIT 1;

/* On our production system where `Customers` is 18068227 rows it claims to look at them all instead of the limit (1000) until the index is added. */

Suggested fix:
The optimiser should try the PRIMARY index.
[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.