List:General Discussion« Previous MessageNext Message »
From:Martin Oldfield Date:May 10 1999 11:57am
Subject:Re: A select query
View as plain text  
In response to this select:

    >> 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;
    >>

>>>>> "Christian" == Christian Mack <Mack@stripped> writes:

    Christian> Mysql has problems with OR's in the WHERE part.  I
    Christian> would try to change the SELECT to this: SELECT
    Christian> 	up.id ,dn.id
    Christian> FROM
    Christian> 	raw AS up , raw AS dn
    Christian> WHERE
    Christian> 	(up.seen = 0 AND up.cmark = dn.cmark AND dn.obstime -
    Christian> 	up.obstime > 0 AND dn.obstime - up.obstime < 3600 ) OR
    Christian> 	(dn.seen = 0 AND up.cmark = dn.cmark AND dn.obstime -
    Christian> 	up.obstime > 0 AND dn.obstime - up.obstime < 3600 )

Whilst this was only a bit faster, the advice about OR was crucial in
finding a much better solution. By splitting the two select into two:

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 )

SELECT
 	up.id ,dn.id
 FROM
 	raw AS up , raw AS dn
 WHERE
 	(up.seen = 1 AND dn.seen = 0
	 AND up.cmark = dn.cmark AND dn.obstime -
 	up.obstime > 0 AND dn.obstime - up.obstime < 3600 ) 

The whole thing runs much faster.

Thanks for your help.

Cheers,
-- 
Martin Oldfield.
Thread
A select queryMartin Oldfield7 May
Re: A select queryChristian Mack7 May
  • Re: A select queryMartin Ramsch7 May
  • Re: A select queryMartin Oldfield10 May