In my experience it was always better to include as much information in one statement as possible. So I created sometimes really complex queries across 5-10 tables and joined them (also using left joins). My Question is this:
If only a few restrictions on some tables in the actual where clause appear, is it faster only to join those restricting tables and execute additional queries for each record in the limited result set, or does MySQL optimize such a thing?
For example:
select
t1.*
,t2.*
,t3.*
,t4.*
from
t1
left join t2 on t1.id = t2.t1Id
left join t3 on t1.id = t3.t1Id
left join t4 on t1.id = t4.t1Id
where
t1.value = 'xy'
and t2.otherValue = 'foo'
limit
0,20
;
as opposed to:
select
t1.*
,t2.*
from
t1
left join t2 on t1.id = t2.t1Id
where
t1.value = 'xy'
and t2.otherValue = 'foo'
limit
0,20
;
and then fetch the values for t3 and t4 separately for each row.
This again may seem a strange question as the first statement makes much more sense...but i was wondering wether MySQL actually optimizes this itself and takes the limit into consideration. It would only need to fetch the data for t3 and t4 for the first 20 found to match the criteria...
any thoughts on this?
Members
Search
Help
Register
Login
Home
