MySQL Forums :: PHP :: How to improve query performance in expression related order by?


Advanced Search

How to improve query performance in expression related order by?
Posted by: jack su ()
Date: April 26, 2009 08:16PM

I have a query, which will return a list of items based on an expression, the expression depend on the input.

Such as I have a list of address, which contains lat and lng, then given one location, I want to find out the roughly nearest 25 records.

so the query will be like:

select * from mytab
where
(lat - 12.34) < 0.1 (lat-12.34) > -0.1 and
(lng - 45.78) < 0.1 and (lng - 45.78) > -0.1
order by (4774.81 * (12.34 - lat) * (12.34 - lat) + 2809 * ( 45.78 - lng) * (45.78 - lng))
limit 25

lat and lng are indexed field, and there are about 2 million records. The response time is 2 sec, apparently not good to scale.

Any suggestion?

Options: ReplyQuote


Subject Written By Posted
How to improve query performance in expression related order by? jack su 04/26/2009 08:16PM
Re: How to improve query performance in expression related order by? Peter Brawley 04/26/2009 10:13PM
Re: How to improve query performance in expression related order by? Rick James 04/27/2009 12:43AM
Re: How to improve query performance in expression related order by? Peter Brawley 04/27/2009 08:32AM
Re: How to improve query performance in expression related order by? jack su 04/27/2009 01:32PM


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.