0

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!

  • The query makes no sense. For every group, it will return an arbitrary, NODE, an arbitary ZONA and an arbitrary DATE. Is that what you want? – ypercubeᵀᴹ Jan 13 '16 at 17:41
  • Well, I've simplified the query here to focus on what's the problem, in the real query there are aggregation operations on the non indexed fields (hence the group by), and I know that the NODE, ZONA and DATE will be arbitrary but that's not really a problem. – goraxan Jan 13 '16 at 18:02
  • Well, please add the real query. The optimizer's choice looks very weird with what you showed. – ypercubeᵀᴹ Jan 13 '16 at 18:04
  • Those explain outputs are with the exact query I've put, let's focus on this query please, for the sake of simplicity. – goraxan Jan 13 '16 at 18:07
  • No, please, lets focurs on a real problem, not on a nonsense query. – ypercubeᵀᴹ Jan 13 '16 at 18:08
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.