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

SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution (type=range vs ALL, possible_keys, key, key_len, rows also differ)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.0
    • Component/s: None
    • Labels:
      None

      Description

      Normal EXPLAIN:

      EXPLAIN SELECT COUNT(*) FROM t1,t2,t3 WHERE e>=c AND d!=b;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	20	
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)

      Variations of SHOW EXPLAIN last line during execution of the query:

      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	5	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	5	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	4	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	4	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	1	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	2	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	2	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	5	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	5	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	range	e	e	4	NULL	5	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)
      1	SIMPLE	t3	ALL	e	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)

      bzr version-info

      revision-id: psergey@askmonty.org-20120524182239-gq6jtnnovydnthtl
      date: 2012-05-24 22:22:39 +0400
      revno: 3405

      Reproducible with the standard optimizer_switch as well as with all OFF values.
      Reproducible with MyISAM, Aria, InnoDB.
      COUNT is not required, it just helps to reduce the amount of data (avoid blocking on net reads).

      MTR test case:

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
       
      CREATE TABLE t2 (b INT, c VARCHAR(1));
      INSERT INTO t2 VALUES
      (2,'v'),(1,'v'),(3,'c'),(2,'a'),(5,'x'),(3,'i'),(1,'e'),
      (4,'p'),(3,'s'),(2,'j'),(6,'z'),(6,'c'),(8,'a'),(2,'q'),
      (6,'y'),(8,'i'),(3,'r'),(3,'v'),(9,'m'),(6,'r');
       
      CREATE TABLE t3 (d INT, e VARCHAR(1), KEY(e));
      INSERT INTO t3 VALUES 
      (2,'x'),(8,'d'),(1,'r'),(9,'f'),(4,'y'),(3,'u'),(2,'m'),
      (5,'m'),(2,'o'),(2,'w'),(6,'m'),(7,'q'),(2,'m'),(5,'d'),
      (7,'g'),(6,'x'),(6,'f'),(2,'p'),(9,'j'),(6,'c');
       
      --connect (con1,localhost,root,,)
       
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN SELECT COUNT(*) FROM t1,t2,t3 WHERE e>=c AND d!=b;
      send SELECT COUNT(*) FROM t1,t2,t3 WHERE e>=c AND d!=b;  
       
      --connection default
       
      let $run = 40;
       
      while ($run)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        --dec $run
      }
       
      --connection con1
      --reap

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.