Michael Widenius wrote:
>
> >>>>> "Jules" == Jules Bean <jmlb2@stripped> writes:
>
> Jules> Christian Mack wrote:
> >>
> >> Martin Oldfield wrote:
>
> Jules> [A complex query question - his query :]
>
> >> >
> >> > select up.id, dn.id from raw up, raw dn
> >> > where (up.seen = 0 or dn.seen = 0)
> >> > and (up.cmark = dn.cmark) and
> >> > (dn.obstime - up.obstime) > 0 and (dn.obstime - up.obstime)
> < 3600;
> >> >
>
> Jules> [Christian replied]
>
> >>
> >> Hi Martin
> >>
> >> Mysql has problems with OR's in the WHERE part.
> >> I would try to change the SELECT to this:
> >> SELECT
> >> up.id
> >> ,dn.id
> >> FROM
> >> raw AS up
> >> , raw AS dn
> >> WHERE
> >> (up.seen = 0
> >> AND up.cmark = dn.cmark
> >> AND dn.obstime - up.obstime > 0
> >> AND dn.obstime - up.obstime < 3600
> >> ) OR
> >> (dn.seen = 0
> >> AND up.cmark = dn.cmark
> >> AND dn.obstime - up.obstime > 0
> >> AND dn.obstime - up.obstime < 3600
> >> )
>
> Jules> Hmm..
>
> Jules> If what christian says is true, the implication is that mysql's query
> Jules> optimiser doesn't normalise logical queries into a consistent form.
> Jules> This surprises me a lot - I've never written a query optimiser, but if I
> Jules> did, I'd do the following:
>
> Jules> 1) Normalise AND and OR relations into a canonical form
>
> The MySQL items makes it quite easy to manipulate AND/OR:s
>
> Jules> 2) Apply a series of well known optimisations to this form
>
> Jules> Under these circumstances, it wouldn't matter which of two logical
> Jules> equivalent queries a user issued.
>
> Jules> Does mysql not work this way?
>
> Hi!
>
> The problem is not that easy. (particularly the 'Apply a series of well
> known optimizations to this form' :)
>
> It's not that hard to allow OR on simply queries, but for the general
> query this is far from easy.
>
> The different 'OR' parts may match the same rows and one must have a
> lot of code to ensure that a particular row combination is not used twice.
> Different OR parts may also be optimized with different table join
> relations. When we start to talk about OR:s within AND:s within OR:s
> the thing quickly gets into a big mess.
>
> MySQL can optimize OR on the same key without any trouble but can't
> yet optimize OR on different keys. As not that many MySQL users seems
> to need this, we have put this rather low on your TODO. This doesn't
> mean that we don't think this feature is unimportant; We only think
> that there are a lot of things that are more important.
>
> Regards,
> Monty
>
The general rule I use is that if I know how to optimize
a query better than the query optimizer I write it in
such a way ( maybe splitting it into several queries) so
that the query optimizer does not have a choice of doing
it any other way than what I tell it until it performs
to my satisfaction.
--
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
