Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5(EOL)
-
None
-
None
Description
Create a table with some rows:
CREATE TABLE table_rows (id INT UNSIGNED NOT NULL PRIMARY KEY);
|
INSERT INTO table_rows (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); |
Here are three queries that are practically equivalent:
1.
SELECT *
|
FROM table_rows
|
WHERE table_rows.id = 10 |
2.
SELECT *
|
FROM table_rows AS outer_table_rows
|
WHERE
|
EXISTS(
|
SELECT *
|
FROM table_rows AS inner_table_rows
|
WHERE
|
outer_table_rows.id = inner_table_rows.id
|
AND
|
inner_table_rows.id = 10 |
)
|
3.
SELECT *
|
FROM table_rows AS outer_outer_table_rows
|
WHERE
|
EXISTS(
|
SELECT *
|
FROM table_rows AS outer_table_rows
|
WHERE
|
outer_outer_table_rows.id = outer_table_rows.id
|
AND
|
EXISTS(
|
SELECT *
|
FROM table_rows AS inner_table_rows
|
WHERE
|
outer_table_rows.id = inner_table_rows.id
|
AND
|
inner_table_rows.id = 10 |
)
|
)
|
Here the third query does not use the constant for the index search. I have found that this can have really bad impact on performance. That will not show for this particular test case, but when a few extra joins are involved in the query blocks, then it could quickly make the query take an infinite time.
Although in this test case the performance issue could be solved by just using the first select, that might not always be an option.
