MySQL Forums
Forum List  »  Performance

Re: select query in from clause creates problem
Posted by: Rick James
Date: April 10, 2010 12:00AM

"use >= or <= instead of between clause" -- they are equivalent.

SELECT TERABITZ_ID
FROM property_ptnf
WHERE latitude BETWEEN ...
AND longitude BETWEEN between -122.118 and -122.036
-- this will scan
INDEX(longitude, latitude, TERABITZ_ID)
from longitude = -122.118 to -122.036, checking each 'row' in the index for the desired value of latitude. When it finds a match it will grab the TERABITZ_ID, again from that index. This is an "index scan". Since an index is (usually) smaller than the data, it can get through it faster. As it gets the 6K TERABITZ_ID values, it will reach for the rest of the stuff (via the JOIN). This means only 6K reaches into the data.

Without the trick, it will gather 31K rows worth of fields that you ask for, only to toss all but 6K of them.

This trick would be a subquery inside your original subquery.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: select query in from clause creates problem
1261
April 10, 2010 12:00AM


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.