-2
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.

Justin Levene
  • 1,630
  • 19
  • 17
  • I'm not sure about IF() function, but CASE certainly would not execute the subquery if WHEN returned false. – forpas May 13 '22 at 14:51
  • As I said, same with a CASE. – Justin Levene May 13 '22 at 17:45
  • *same* what? CASE does not execute a subquery when it does not need to. Check this post: https://stackoverflow.com/a/59258713/10498828 – forpas May 13 '22 at 17:55
  • I tested with a case and it does execute, as well as evaluate. See the comments in your link – Justin Levene May 14 '22 at 17:58
  • The comments in my link state that CASE works as expected and as documented. Check the code in that answer. Also, check this: https://www.db-fiddle.com/f/dHxAfVH7As125wh66rkrJ7/0 and this: https://www.db-fiddle.com/f/ewrXZ2nLKtcwsZsA8cnha/0 If you can reproduce the behavior of CASE that you claim that you experience then post a fiddle with that code. – forpas May 14 '22 at 18:05
  • What "proof" do you have that it executed the subquery? (I tried a different query and came to the opposite conclusion.) – Rick James May 19 '22 at 22:15
  • You could add `SLEEP(1)` to the inner select list, thereby slowing down the query. – Rick James May 19 '22 at 22:23

2 Answers2

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
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • This does not solve or address the issue as you are still in essence executing the sub query, this time as a left join. – Justin Levene May 13 '22 at 17:45
  • @JustinLevene Joins are better than sub query most of the times; and in this case definitely. Also JOINS <> Sub query. Please search and you'll see multiple reasons why we should do a join instead of sub query. – DhruvJoshi May 14 '22 at 05:00
  • Because with a join, the search is done on all rows, the idea of a sub query is that the search will only be done on rows that require it, however MySQL is not behaving. – Justin Levene May 16 '22 at 17:41
-1

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
UioSun
  • 59
  • 5