IF statement sub query optimisation in MySQL-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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