IF statement optimisation in MySQL-CodePudding

select
if(`num`>99, (select `column` from `tbl` where `id`=`num`), "") as result
from `main_table`

This all works fine but MySQL is very inefficient as it executes the tbl select statement when num is below 99. Is there a way to make it more efficient as this obviously slows down the command when executing a supplemental select that doesn't need to be executed.

I tried a case which has exactly the same 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