0

in this query https://www.db-fiddle.com/f/snzSLd8y1oRNRqdsFpNdHa/0

SELECT test0.id,
(SELECT GROUP_CONCAT(test2.one_info) FROM test2 WHERE test2.one_id = test1.id) AS GC
FROM test0
INNER JOIN test1 ON test1.zero_id = test0.id
WHERE test0.id = 1 AND test1.id = 1 AND test1.rand = 5;

test1.rand=5 exists so the execution plan is

| id  | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
| --- | ------------------ | ----- | ---------- | ----- | ------------- | ------- | ------- | ----- | ---- | -------- | ----------- |
| 1   | PRIMARY            | test0 |            | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      | Using index |
| 1   | PRIMARY            | test1 |            | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      |             |
| 2   | DEPENDENT SUBQUERY | test2 |            | ref   | IND           | IND     | 5       | const | 4    | 100      | Using index |

but when i change it to something that does not exist like test1.rand=10 it give this exectuion plan

| id  | select_type        | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra                                               |
| --- | ------------------ | ----- | ---------- | ---- | ------------- | --- | ------- | ----- | ---- | -------- | --------------------------------------------------- |
| 1   | PRIMARY            |       |            |      |               |     |         |       |      |          | Impossible WHERE noticed after reading const tables |
| 2   | DEPENDENT SUBQUERY | test2 |            | ref  | IND           | IND | 5       | const | 4    | 100      | Using index                                         |

and despite Extra: Impossible WHERE noticed after reading const tables and it returning no results it still used select_type: DEPENDANT SUBQUERY and got rows: 4

how to fix this problem and make it not return anything at all on Extra: Impossible WHERE noticed after reading const tables to save performance?

i tried dervied subquery like this

SELECT test0.id, GC.GC
FROM test0
INNER JOIN test1 ON test1.zero_id = test0.id
INNER JOIN (SELECT GROUP_CONCAT(test2.one_info) AS GC FROM test2 WHERE test2.one_id = 1) AS GC
WHERE test0.id = 1 AND test1.id = 1 AND test1.rand = 10;

but did same problem

| id  | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra                                               |
| --- | ----------- | ----- | ---------- | ---- | ------------- | --- | ------- | ----- | ---- | -------- | --------------------------------------------------- |
| 1   | PRIMARY     |       |            |      |               |     |         |       |      |          | Impossible WHERE noticed after reading const tables |
| 2   | DERIVED     | test2 |            | ref  | IND           | IND | 5       | const | 4    | 100      | Using index                                         |
Joe Doe
  • 523
  • 2
  • 9
  • Have a look at mysql order of execution.https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order – P.Salmon Apr 25 '20 at 10:57
  • @P.Salmon in question they said order is `FROM > WHERE > SELECT` and the subquery is in `SELECT` so `WHERE` should stop it from running - right? – Joe Doe Apr 25 '20 at 11:03

0 Answers0