MySQL Forums
Forum List  »  Newbie

Re: Slow select with sub queries
Posted by: Chad Bourque
Date: December 01, 2010 08:25AM

Tom,

In your query, the second subquery appears to be superfluous. The first query should be replaced with a simple inner join (as LA suggested). Something like this:

select distinct m.ID
  from Main m
    join Coordinates c
      on m.ID = c.Main_ID
  where m.Status = '1'
    and m.Public = '1'
    and c.Lon between -149.843812 and -149.369494
    and c.Lat between 61.013580 and 61.129084;

You could also move the between conditions to the on clause if you prefer:

select distinct m.ID
  from Main m
    join Coordinates c
      on m.ID = c.Main_ID
        and c.Lon between -149.843812 and -149.369494
        and c.Lat between 61.013580 and 61.129084
  where m.Status = '1'
    and m.Public = '1';

HTH,
Chad
Milamade, LLC - Custom Software Development

Options: ReplyQuote


Subject
Written By
Posted
December 01, 2010 03:00AM
Re: Slow select with sub queries
December 01, 2010 08:25AM
December 01, 2010 01:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.