Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4423

GROUP BY not using index

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 5.5.30
    • None
    • None
    • Linux, Windows

    Description

      Query on table dt1 with index on 'nr':

      select max(dt1) from gb1 group by nr;

      not using this index for 'group by'.

      The classic MySQL-u do not have this problem.

      #example

      CREATE TABLE `gb1` (
          `dt1` datetime DEFAULT NULL,
          `nr` int(11) DEFAULT NULL,
          PRIMARY KEY (`nr` ASC , `dt1` ASC)
      )  ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
      insert ignore into gb1(dt1,nr) values(now() - interval 1 second,rand()*1000);
      /*20 x*/ insert ignore into gb1(dt1,nr) select  dt1 - interval rand() * 10000 second, rand()*1000 from gb1;
       
      explain select max(dt1) from gb1 group by nr;
      explain select max(dt1) from gb1 use key for group by(PRIMARY) group by nr;

      Attachments

        Activity

          People

            elenst Elena Stepanova
            Misio Misio Rysio
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.