Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1820

LP #1713937: SELECT DISTINCT doesn't return result with "Using index for group-by" optimization

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Jaime Sicam last update 31-08-2017 02:38:18

      Unfortunately, I can't reproduce the same issue without using index hints.

      Test data:
      mysql> use test;
      mysql> create table t1(a int not null auto_increment primary key, b int not null,c int not null, unique(b,c));

      mysql>insert into t1(b,c) values(1,1),(1,2),(1,3),(1,4),(1,5);
      mysql>insert into t1(b,c) values(2,1),(2,2),(2,3),(2,4),(2,5);
      mysql>insert into t1(b,c) values(3,1),(3,2),(3,3),(3,4),(3,5);
      mysql>insert into t1(b,c) values(4,1),(4,2),(4,3),(4,4),(4,5);

      select * from t1;
      ------

      a b c

      ------

      1 1 1
      2 1 2
      3 1 3
      4 1 4
      5 1 5
      6 2 1
      7 2 2
      8 2 3
      9 2 4
      10 2 5
      11 3 1
      12 3 2
      13 3 3
      14 3 4
      15 3 5
      16 4 1
      17 4 2
      18 4 3
      19 4 4
      20 4 5

      ------
      20 rows in set (0.00 sec)

      Good result:
      mysql> select distinct b from t1 where a in (5,7);
      ---

      b

      ---

      1
      2

      ---
      2 rows in set (0.00 sec)

      mysql> explain select distinct b from t1 where a in (5,7);
      ----------------------------------------------------------------------------------------------------------+

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra

      ----------------------------------------------------------------------------------------------------------+

      1 SIMPLE t1 NULL range PRIMARY,b PRIMARY 4 NULL 2 100.00 Using where; Using temporary

      ----------------------------------------------------------------------------------------------------------+
      1 row in set, 1 warning (0.00 sec)

      Empty result:
      mysql> select distinct b from t1 use index(b) where a in (5,7);
      Empty set (0.00 sec)

      mysql> explain select distinct b from t1 use index(b) where a in (5,7);
      ----------------------------------------------------------------------------------------------------------------+

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra

      ----------------------------------------------------------------------------------------------------------------+

      1 SIMPLE t1 NULL range b b 4 NULL 5 20.00 Using where; Using index for group-by

      ----------------------------------------------------------------------------------------------------------------+
      1 row in set, 1 warning (0.00 sec)

      mysql> show warnings;
      ----------------------------------------------------------------------------------------------------------------------------------

      Level Code Message

      ----------------------------------------------------------------------------------------------------------------------------------

      Note 1003 /* select#1 */ select distinct `test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX (`b`) where (`test`.`t1`.`a` in (5,7))

      ----------------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      This will be a big issue if the optimizer chose unique index b instead of the primary key in production.
      If you're not able to reproduce it yet, perhaps MySQL chose this optimize the query with "Using where; Using index"
      Try restarting the MySQL instance or add another set of rows:
      insert into t1(b,c) values(5,1),(5,2),(5,3),(5,4),(5,5);
      Then try running the query again.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              lpjirasync lpjirasync (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: