List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 11 1999 4:47pm
Subject:Re: Mysql doesn't normalise it's logic? (was Re: A select query)
View as plain text  
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)
Thread
Mysql doesn't normalise it's logic? (was Re: A select query)Jules Bean8 May
  • Mysql doesn't normalise it's logic? (was Re: A select query)Michael Widenius11 May
Re: Mysql doesn't normalise it's logic? (was Re: A select query)Sasha Pachev11 May