Bug #77276 force index not working with groupby+orderby
Submitted: 9 Jun 2015 3:27 Modified: 28 Jul 2015 3:41
Reporter: bombzj bombzj Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6.25, 5.6.26 OS:Linux (Centos5)
Assigned to: CPU Architecture:Any
Tags: force index

[9 Jun 2015 3:27] bombzj bombzj
Description:
force index(xxx) not working in certain condition.

How to repeat:
CREATE TABLE  `table5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `cata` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `useridx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into table5 (user_id,cata) values (1, 1);
insert into table5 (user_id,cata) values (2, 1);
insert into table5 (user_id,cata) values (3, 1);
insert into table5 (user_id,cata) values (4, 2);
insert into table5 (user_id,cata) values (5, 2);
insert into table5 (user_id,cata) values (6, 2);
insert into table5 (user_id,cata) values (7, 3);
insert into table5 (user_id,cata) values (8, 3);
insert into table5 (user_id,cata) values (9, 4);
insert into table5 (user_id,cata) values (10, 4);

explain SELECT * FROM table5 force index(useridx) where user_id between 2 and 4 group by cata order by cata limit 1;

-- ok so far, with key useridx

ALTER TABLE `table5` ADD INDEX `cataidx`(`cata`);

explain SELECT * FROM table5 force index(useridx) where user_id between 2 and 4 group by cata order by cata limit 1;

-- type=ALL and no key used
[9 Jun 2015 9:14] MySQL Verification Team
Hello!

Thank you for the report and test case.

Thanks,
Umesh
[9 Jun 2015 9:41] Øystein Grøvlen
Posted by developer:
 
If you turn off ONLY_FULL_GROUP_BY, you will see that this works as intended in 5.7:

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT * FROM table5 force index(useridx) where user_id between 2 and 4 group by cata order by cata limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table5
   partitions: NULL
         type: range
possible_keys: useridx,cataidx
          key: useridx
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
[24 Jul 2015 9:45] Guilhem Bichot
Closing, as the testcase works fine in 5.7.
[28 Jul 2015 3:41] bombzj bombzj
the bug is out there ( 5.6.26 )
[28 Jul 2015 8:59] Guilhem Bichot
yes, it exists in 5.6.26, is fixed in 5.7.8, and wasn't classified as "so
severe bug that it must be fixed in 5.6 no matter the risk of destabilizing
something else in 5.6-GA". Like many software projects, not all bugs are
fixed in the GA. That depends on several factors. 5.7.8 is RC2.