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

LP:1002508 - The number of expected rows to be examined is off for a query with ORDER BY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • 5.5.28a
    • None

    Description

      If to create and populate tables t1,t2 with the commands

      CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
      INSERT INTO t1 VALUES
      (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
      CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
      INSERT INTO t2 VALUES
      (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
      (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);

      then Mariadb 5.5 will return the following explain output for the query
      SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a

      MariaDB [test]> EXPLAIN
      -> SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
      ------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

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

      1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
      1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index

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

      The expected number of the examined rows from t1 is 4 though it should be 8 as for the following query:

      MariaDB [test]> EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
      ------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

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

      1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index
      1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using index

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

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.