CODEWITHSUNDEEP
mysqlsql
qwaz
qwaz

Reputation: 1305

MySql faster to use separate query or subquery?

The following query took 0.2630 sec to select 2477 rows from the total of 85862 rows in the table

(SELECT 1, `name` FROM `tab1` WHERE `reg` = 5 and `lang` = 9 and `queue` = 
   (select min(`queue`) from tab1 where `reg` = 5 and `lang` = 9)
limit 5)
UNION ALL
(SELECT 2, `name` FROM `tab1` WHERE `reg` = 1 and `lang` = 3 and `queue` =
   (select min(`queue`) from tab1 where `reg` = 1 and `lang` = 3)
limit 5)
UNION ALL
(SELECT 3, `name` FROM `tab1` WHERE `reg` = 3 and `lang` = 11 and `queue` =
   (select min(`queue`) from tab1 where `reg` = 3 and `lang` = 11)
limit 5)

Will there be, theoretically, any benefit to performance if, in order to detect MIN(value) of a column, I will run a separate query instead of sub-query?

columns reg and lang are indexed, queue is not

EXPLAIN:

| id | select_type | table       | type        | possible_keys | key      | key_len | ref  | rows| Extra
-----+-------------+-------------+-------------+---------------+----------+---------+------+-----+---------------------------------------
| 1  | PRIMARY     | tab1        | index_merge | reg,lang      | lang,reg | 1,1     | NULL | 813 | Using intersect(lang,reg); Using where
| 2  | SUBQUERY    | tab1        | index_merge | reg,lang      | lang,reg | 1,1     | NULL | 813 | Using intersect(lang,reg); Using where
| 3  | UNION       | tab1        | index_merge | reg,lang      | lang,reg | 1,1     | NULL | 830 | Using intersect(lang,reg); Using where
| 4  | SUBQUERY    | tab1        | index_merge | reg,lang      | lang,reg | 1,1     | NULL | 830 | Using intersect(lang,reg); Using where
| 5  | UNION       | tab1        | index_merge | reg,lang      | lang,reg | 1,1     | NULL | 834 | Using intersect(lang,reg); Using where
| 6  | SUBQUERY    | tab1        | index_merge | reg,lang      | lang,reg | 1,1     | NULL | 834 | Using intersect(lang,reg); Using where
|NULL| UNION RESULT| <union1,3,5>|  ALL        | NULL          | NULL     | NULL    | NULL | NULL| Using temporary

Upvotes: 0

Views: 161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

MySQL should be running the "min" subquery only once for each group. Putting the value in a separate variable shouldn't really affect performance.

If you want to optimize the performance of this query, then create an index on tab1(reg, lang, queue). This index will allow direct lookups for values without going through the "index merge" step.

Upvotes: 2

Related Questions