Due to MySQL subquery handling, sometimes a subquery in the where clause causes significant performance issues compared the semantically identical join with a subquery.
Consider these two semantically equivalent queries;
vs
Currently I'm exploiting that you can stick arbitrary SQL into Finder::$indexHints to implement this. The existing Finder::$joins can't be used due to the escaping around
In my particular case, converting from a subquery in the where clause to a join drops a complex query from 0.2 seconds to 0.07 seconds.
Consider these two semantically equivalent queries;
SQL:
select *
from table
where id in (select id from table2)
SQL:
select *
from table
join (select id from table2) a on a.id = table.idCurrently I'm exploiting that you can stick arbitrary SQL into Finder::$indexHints to implement this. The existing Finder::$joins can't be used due to the escaping around
`$join[table]` which prevents an arbitrary SQL blob to be added.In my particular case, converting from a subquery in the where clause to a join drops a complex query from 0.2 seconds to 0.07 seconds.
Upvote
1
