MySQL optimizer difference between 5.6.27 and 5.5

Question

We've just updated the MySQL version of the database of one of our clients from the 5.5 to the 5.6, everything is working fine except one simple query which is causing us troubles.

Having the following table:

CREATE TABLE `MY_TABLE` (
  `ID` bigint(20) NOT NULL,
  `DATE` datetime DEFAULT NULL,
  `NODE` varchar(50) DEFAULT NULL,
  `CELL` varchar(50) DEFAULT NULL,
  `ZONA` tinyint(4) DEFAULT NULL,
  `non_indexed_fields...`
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DATE` (`DATE`,`NODE`,`CELL`,`ZONA`),
  KEY `CELL` (`CELL`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want the following query to use the CELL index, the only possible in 5.5 according to the EXPLAIN command, and impossible to use now with the 5.6 (even forcing by it with the clause FORCE/USE INDEX(CELL) )

SELECT 
    this_.DATE AS y0_,
    this_.NODE AS y1_,
    this_.CELL AS y2_,
    this_.ZONA AS y3_
FROM
    MY_TABLE this_
WHERE
    (this_.CELL LIKE '2817%')
GROUP BY this_.CELL , HOUR(this_.DATE)
ORDER BY y0_ DESC
LIMIT 50

This is the explain output for the 5.5 version (only 1 elegible index):

+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | this_ | range | CELL          | CELL | 53      | NULL | 5551 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+

And for the 5.6 version (2 elegible indexes):

+----+-------------+-------+-------+---------------+------+---------+------+-----------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows      | Extra                                                     |
+----+-------------+-------+-------+---------------+------+---------+------+-----------+-----------------------------------------------------------+
|  1 | SIMPLE      | this_ | index | DATE,CELL     | DATE | 117     | NULL | 145283968 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+-----------+-----------------------------------------------------------+

And if I force the use of CELL index this is what explain says:

+----+-------------+-------+------+---------------+------+---------+------+-----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows      | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+-----------+----------------------------------------------+
|  1 | SIMPLE      | this_ | ALL  | DATE,CELL     | NULL | NULL    | NULL | 145332659 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----------+----------------------------------------------+

The interesting thing is that if I change the sorting column to another different than y0_ it works. Same if I remove the LIMIT clause.

+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                                               |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | this_ | range | DATE,CELL     | CELL | 53      | NULL | 5551 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------------------------------+

If I change the DATE index by removing the DATE field from it (or changing the order so that it's not the first) then it's not an elegible key and the CELL index it's used just fine.

After the hole day trying to know what's going here I'm lost. Why MySQL decides it's better to scan 145283968 results rather than 5551? Why it won't let me force the use of the CELL index?

Any thoughts?

Thanks very much!

Answer 1

ORDER BY y0_ DESC does not make sense when you have GROUP BY this_.CELL , HOUR(this_.DATE). ORDER BY HOUR(this_.DATE) DESC makes more sense; change to that. I suspect the optimizer will then 'correctly' pick the CELL index.

Another problem... It is not 'proper' to select several non-aggregate values but GROUP BY only a subset of them. Which NODE and ZONA do you want displayed? You will get a random one. You could change to MAX(NODE), etc. You say that NODE and ZONA; does this mean that the UNIQUE key is over-specified? Perhaps it should be just UNIQUE(DATE, CELL).

I bring up all these issues because they may be misleading the Optimizer into making poor decisions, such as the one you are experiencing.