'IF statement sub query optimisation in MySQL
select
if(`num`>99, (select `value` from `sub` where `id`=`main`.`num`), "") as `result`
from `main`
This all works fine but MySQL is very inefficient as it needlessly executes the sub
select statement when num
is below 100, even though the result of the sub query will not be used when num
is below 100.
Is there a way to make it more efficient so the sub query is not executed when the if statement is false, as this obviously slows down the command by executing a sub query that doesn't need to be executed.
I tried a CASE which has exactly the same issue.
TO BE CLEAR: This is about MySQL executing a sub query when it doesn't need to. This is a waste of resources and slows down the overall query, thus very inefficient. The statement above I just made up to simply highlight the sub query being needlessly executed issue.
Solution 1:[1]
you could use a join like below
select
case when `num`>99 then t.`column` else '' end as result
from `main_table` m
left join `tbl` t
on m.`num`=t.`id` and `num`>99
Solution 2:[2]
the empty ("") is requred?
if not:
(select `column` from `tbl` where `id` = `num`) as result
FROM `main_table`
WHERE `num` > 99
or use JOIN select (I not run it, only reference):
SELECT column AS result
FROM tbl
LEFT JOIN main_table
ON tbl.id = main_table.num
WHERE main_table.num > 99
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | DhruvJoshi |
Solution 2 | UioSun |