Which does the optimiser handle better?
SELECT TBL.*,J.* FROM (
(SELECT ...,T.ThingId FROM ...)
UNION
(SELECT ...,T.ThingId FROM ...)
) TBL
LEFT JOIN ThingDetails TD USING(ThingId)
....
Or
(SELECT ...,T.ThingId,TD.* FROM ... LEFT JOIN ThingDetails TD USING(ThingId))
UNION
(SELECT ...,T.ThingId,TD.* FROM ... LEFT JOIN ThingDetails TD USING(ThingId))
I would imagine in simple cases that it doesn't matter much, but I wondered what recommendations you guys may have. I don't want to experiment much here because of the time taken to actually write test queries and even then they'll be very specific cases. I'd like to know what the optimiser can apply here.
As an example of an optimisation I know through experience, replacing an OR
with a XOR
if you know both operands cannot both be true can help!
I'd also be curious to know about WHERE
statements, On each part of the union, or outside? Some testing shows that it actually may matter (an <autokey0>
pops up if I use a where outside of union) - I shall keep investigating but a point in the right direction would be great!
Any information on MariaDB or MySQL is welcome. I'm not looking for specific behaviour in each version and going back as far as 5.1 at least MySQL is able to factor in and out various constraints, I'm looking for detailed optimiser behaviour.
My own limited testing showed that MySQL both factored in some joins and factored out others. In fact one of my tests involved MySQL returning the first union in the order of an index of the table it was joining to's primary key. The as-if rule states that any optimisation is okay as long to the observer it is as if what they wrote actually happened. I've had problems with this before. It was actually a bug in MySQL 5.1 - I now know it's because of how InnoDB works.
This question is really about what 'confuses' the optimiser enough to not be able to make these observations.