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

EXISTS() slower if ORDER BY is defined

    Details

      Description

      In some specific cases, EXISTS() will slowdown if ORDER BY is defined in the subquery. Why is the reason to apply an ORDER BY to EXISTS()? None. But Laravel does that and I can't control it.

      The question is: ORDER BY inside of an EXISTS() will slowdown in a specific case. I think that the optimizer is not ignoring that.

      Setup:

      CREATE TABLE IF NOT EXISTS `table_a` (
      	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      	PRIMARY KEY (`id`)
      )
      ENGINE=InnoDB;
       
      CREATE TABLE IF NOT EXISTS `table_b` (
      	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      	`index_a` INT(10) UNSIGNED NULL DEFAULT NULL,
      	`index_b` INT(10) UNSIGNED NULL DEFAULT NULL,
      	`index_c` INT(10) UNSIGNED NULL DEFAULT NULL,
      	`parameter` INT(10) UNSIGNED NULL DEFAULT NULL,
      	PRIMARY KEY (`id`),
      	INDEX `index_a_index_b` (`index_a`, `index_b`),
      	INDEX `index_c` (`index_c`),
      	INDEX `parameter` (`parameter`)
      )
      ENGINE=InnoDB;
      

      Populate:

      DELIMITER ;;
      FOR i IN 1 .. 1250
      DO
        INSERT INTO table_a VALUES ();
        INSERT INTO table_b (index_a, index_b, index_c, parameter) VALUES (123, i, NULL, i);
      END FOR;;
      DELIMITER ;
      

      Slow query:

      SELECT table_a.id
      FROM   table_a 
       
      WHERE
      	EXISTS (
      		SELECT *
      		FROM table_b
      		
      		WHERE table_b.index_a = 123 AND
      			  table_b.index_b = table_a.id AND
      			  table_b.parameter = 123
       
      		ORDER BY table_b.index_c
      	);
      

      Cleanup:

      DROP TABLE IF EXISTS table_a;
      DROP TABLE IF EXISTS table_b;
      

      What I realized:

      • Dropping ORDER BY will solve (which is not an option for me, unfortunatelly);
      • Dropping index index_a_index_b or index_c will solve;
      • Moving index index_a_index_b to end will solve;
      • Split index index_a_index_b into two separated indexes will solve;

      And about the parameter column:

      I created this column just to make sure that it will returns only the `table_a.id = 123` as an additional parameter. In this case, I could run a different query with the same behaviour using `IN()` that will not will slowdown (0.000s vs. 1.516s for 1250 x 1250 rows).

      SELECT table_a.id
      FROM   table_a
       
      WHERE
      	table_a.id IN (
      		SELECT table_b.parameter
      		FROM table_b
      		
      		WHERE table_b.index_a = 123 AND
      			   table_b.index_b = table_a.id AND
      			   table_b.parameter = 123
      		
      		ORDER BY table_b.index_c
      	)
      

      Explain:

      The EXPLAIN is a bit different.

      • The PRIMARY is the same: table_a, type index, possible keys NULL, key PRIMARY, key length 4, ref NULL, rows 1445, extra using where; using index;
      • The DEPENDENT SUBQUERY will have some equal values, like table table_b, possible keys index_a_index_b,parameter, rows 1 and extra using where;
      • The DEPENDENT SUBQUERY of EXISTS() query will be type index, key index_c, key length 5 and ref NULL;
      • The DEPENDENT SUBQUERY of IN() query will be type ref, key index_a_index_b, key length 10 and ref const,test.table_a.id;

      About ORDER BY insde EXISTS()

      This query is generated by Laravel framework when we use the whereHas() method. Because of a Scope it add the ORDER BY in this place. As workaround I have modified the code to use IN() instead of EXISTS(), but I think that it could be optimized to work fine in all the cases (even with ORDER BY).

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              rentalhost David Rodrigues
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: