EXISTS() slower if ORDER BY is defined

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).