How to force MySQL "Using index for group-by"

Question

I use MySQL 5.6.19-log (according to select version()).

I have a InnoDB table something like this:

CREATE TABLE `mytable` (
  `foo_id` bigint(20) NOT NULL,
  `bar_id` bigint(20) NOT NULL,
  `baz_id` bigint(20) NOT NULL,
  PRIMARY KEY (`foo_id`,`bar_id`,`baz_id`)
)

This table works well with the following query:

select
    foo_id,
    min(bar_id)-1 
from
    mytable 
where
    foo_id IN (
        1000,2000
    ) 
group by
    foo_id;

+----+-------------+----------------------+-------+------------------------+---------+---------+------+-------+---------------------------------------+
| id | select_type | table                | type  | possible_keys          | key     | key_len | ref  | rows  | Extra                                 |
+----+-------------+----------------------+-------+------------------------+---------+---------+------+-------+---------------------------------------+
|  1 | SIMPLE      | mytable              | range | PRIMARY,bar_id_idx     | PRIMARY | 8       | NULL | 58245 | Using where; Using index for group-by |
+----+-------------+----------------------+-------+------------------------+---------+---------+------+-------+---------------------------------------+

+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                         |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00036575 | select foo_id, min(bar_id)-1 from mytable where foo_id IN (1000,2000) group by foo_id                                                         |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+

But, when the number of foo_id in the where clause is only one, the query becomes quite slow, as follows:

select
    foo_id,
    min(bar_id)-1 
from
    mytable 
where
    foo_id = 1000 
group by
    foo_id;

+----+-------------+----------------------+------+------------------------+---------+---------+-------+--------+-------------+
| id | select_type | table                | type | possible_keys          | key     | key_len | ref   | rows   | Extra       |
+----+-------------+----------------------+------+------------------------+---------+---------+-------+--------+-------------+
|  1 | SIMPLE      | mytable              | ref  | PRIMARY,bar_id_idx     | PRIMARY | 8       | const | 873664 | Using index |
+----+-------------+----------------------+------+------------------------+---------+---------+-------+--------+-------------+

+----------+------------+-----------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                           |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
|        1 | 0.07258075 | select foo_id, min(bar_id)-1 from mytable where foo_id = 1000 group by foo_id                                   |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+

I think something went wrong with MySQL's query planner. Is there any hint or something to force MySQL to use index for group by, when the number of foo_id is only one? I tried analyze table mytable but it doesn't help.

I know that a query select min(bar_id)-1 from mytable where foo_id = 1000 is fast when the number of foo_id is only one, but it makes a branch to the code of my app, so I'd like to avoid this.

Answer 1

its not the answer. its only a sample from my comment

sample

mysql> EXPLAIN select
    ->     foo_id,
    ->     min(bar_id)-1
    -> from
    ->     mytable
    -> where
    ->     foo_id IN (
    ->         1000,2000
    ->     )
    -> group by
    ->     foo_id;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | mytable | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

mysql>
mysql> EXPLAIN select
    ->     foo_id,
    ->     min(bar_id)-1
    -> from
    ->     mytable
    -> where
    ->     foo_id = 1000
    -> group by
    ->     foo_id;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | ref  | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.14    |
+-----------+
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE mytable;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                   |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
  `foo_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bar_id` bigint(20) NOT NULL,
  `baz_id` bigint(20) NOT NULL,
  PRIMARY KEY (`foo_id`,`bar_id`,`baz_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12804 DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

Answer 2

Try

select
foo_id,
min(bar_id)-1 
from
    mytable 
where
    foo_id LIKE 1000 
group by
    foo_id;