MySQL Forums :: Newbie :: WHERE OR performance issue


Advanced Search

WHERE OR performance issue
Posted by: Lee Fear ()
Date: March 30, 2012 03:33AM

I have two sql statements which achieve the same thing but have vastly different performance and I was just wondering why this is.

The first statement is as follows.

SELECT *
FROM tbl_sectordistances
WHERE (destinationsectorid=300 AND distance<=20) OR (originsectorid=300 AND distance<=20);

I end up aborting this query after around 4 or 5 mins as I get fed up of waiting.

However the other statement is as follows

SELECT *
FROM tbl_sectordistances
WHERE (destinationsectorid=300 AND distance<=20)
UNION
SELECT *
FROM tbl_sectordistances
WHERE (ORIGINSECTORID=300 AND distance<=20);

this returns the results in less than 1/4 or a second!

Why does the OR statement hit the performance so hard?

Options: ReplyQuote


Subject Written By Posted
WHERE OR performance issue Lee Fear 03/30/2012 03:33AM
Re: WHERE OR performance issue laptop alias 03/30/2012 05:07AM
Re: WHERE OR performance issue Lee Fear 03/30/2012 05:43AM
Re: WHERE OR performance issue laptop alias 03/30/2012 07:29AM
Re: WHERE OR performance issue Lee Fear 03/30/2012 09:02AM
Re: WHERE OR performance issue Lee Fear 03/30/2012 09:03AM
Re: WHERE OR performance issue Lee Fear 03/30/2012 09:05AM
Re: WHERE OR performance issue Peter Brawley 03/30/2012 09:30AM
Re: WHERE OR performance issue Lee Fear 03/30/2012 09:38AM
Re: WHERE OR performance issue Peter Brawley 03/30/2012 10:02AM
Re: WHERE OR performance issue Lee Fear 03/30/2012 12:51PM
Re: WHERE OR performance issue Peter Brawley 03/30/2012 08:13AM
Re: WHERE OR performance issue Rick James 04/01/2012 01:04PM


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.