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

CHEAP SQ: A query with inner joins and EXISTS subquery takes several times longer on MDEV-193 tree than on 5.5 main tree

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • None
    • 5.5.27
    • None
    • None

    Description

      The following query

      SELECT MAX(al1.a) 
      FROM t1 AS al1, t1 AS al2, t1 AS al3, t1 AS al4
      WHERE al4.a = al3.a 
        AND (
          EXISTS ( SELECT 1 FROM t1 )
          OR al1.a > al2.a
        )

      takes several times longer on MDEV-193 tree revno 3407 comparing to maria/5.5 tree 3426.

      Reproducible with the default optimizer_switch as well as all OFF values except for in_to_exists required to execute the query.
      Reproducible with MyISAM (~3 times longer, 30 vs 10 sec on my machine), Aria and InnoDB (~6 times longer, 60 vs 10 sec).

      EXPLAIN on MDEV-193 (with the default optimizer_switch):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	al1	index	NULL	a	5	NULL	90	100.00	Using index
      1	PRIMARY	al2	index	NULL	a	5	NULL	90	100.00	Using index; Using join buffer (flat, BNL join)
      1	PRIMARY	al3	index	a	a	5	NULL	90	100.00	Using where; Using index; Using join buffer (incremental, BNL join)
      1	PRIMARY	al4	ref	a	a	5	test.al3.a	10	100.00	Using index
      2	SUBQUERY	t1	index	NULL	a	5	NULL	90	100.00	Using index
      Warnings:
      Note	1003	select max(`test`.`al1`.`a`) AS `MAX(al1.a)` from `test`.`t1` `al1` join `test`.`t1` `al2` join `test`.`t1` `al3` join `test`.`t1` `al4` where (`test`.`al4`.`a` = `test`.`al3`.`a`)

      EXPLAIN on maria/5.5 (with the default optimizer_switch):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	al3	index	a	a	5	NULL	90	100.00	Using where; Using index
      1	PRIMARY	al4	ref	a	a	5	test.al3.a	10	100.00	Using index
      1	PRIMARY	al1	index	a	a	5	NULL	90	100.00	Using index; Using join buffer (flat, BNL join)
      1	PRIMARY	al2	index	a	a	5	NULL	90	100.00	Using where; Using index; Using join buffer (incremental, BNL join)
      2	SUBQUERY	t1	index	NULL	a	5	NULL	90	100.00	Using index
      Warnings:
      Note	1003	select max(`test`.`al1`.`a`) AS `MAX(al1.a)` from `test`.`t1` `al1` join `test`.`t1` `al2` join `test`.`t1` `al3` join `test`.`t1` `al4` where ((`test`.`al4`.`a` = `test`.`al3`.`a`) and (exists(select 1 from `test`.`t1`) or (`test`.`al1`.`a` > `test`.`al2`.`a`)))

      Test case:

      SET optimizer_switch = 'in_to_exists=on';
       
      CREATE TABLE t1 (a INT, KEY(a));
      INSERT INTO t1 VALUES
      (7),(5),(1),(204),(224),(9),(5),(0),(3);
       
      INSERT INTO t1 SELECT al1.* FROM t1 al1, t1 al2;
       
      SELECT MAX(al1.a) 
      FROM t1 AS al1, t1 AS al2, t1 AS al3, t1 AS al4
      WHERE al4.a = al3.a 
        AND (
          EXISTS ( SELECT 1 FROM t1 )
          OR al1.a > al2.a
        );

      Attachments

        Issue Links

          Activity

            People

              timour Timour Katchaounov (Inactive)
              elenst Elena Stepanova
              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.