List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 11 1999 9:44am
Subject:Mysql doesn't normalise it's logic? (was Re: A select query)
View as plain text  
>>>>> "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
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