Topic awaiting preservation: improving speed of mysql distance filter (Page 1 of 1)

Hello!

I am creating a distance filter for use in my classifieds site, Safarri.com
Users are able to enter their zip code, and then show ads near to them.
I am currently using the below sql to do this:

SELECT {fields}, SQRT( (69.1 * ('48.055621' - a.lat)) * (69.1 * ('48.055621' - a.lat)) + (53.0 *('-116.585400' - a.lon)) * (53.0 *('-116.585400' - a.lon))) as distance
FROM {tables}
WHERE {criteria} HAVING distance < '{distance}'  ORDER BY distance ASC

I was thinking that it might be innefficient to do this. After all, a complex mathmatical filter must be applied to every row in the database to determine it if meets the criteria.
I am thinking that adding the below code to my sql query might increase its speed. I am thinking that mysql will first compare the indexed lat and lons to the "box", and then do the "heavy" mathmatics on the subset which matched its original "box" criteria. Will mysql do this?

WHERE a.lat > a.lat - '{X}' AND a.lat < a.lat + '{X}' a.lon > a.lon - '{X}' AND a.lon < a.lon + '{X}' {criteria}

Thanks for your suggestions,

-Robur

P.S. How do i determine {X}? Is it (({distance} * 53.0) / 2)?

check the FAQ: http://www.ozoneasylum.com/21781
And this thread: http://www.ozoneasylum.com/21687

You might find some valuable input there
/D

{cell 260} {Blog}
-{" Computer:
?As a Quantum Supercomputer I take advantage of Zeno?s paradox?
C: ?Imagine a photon that must travel from A to B. The photon travels half the distance to B. From its new location it travels half the new distance. And again, it travels half that new distance.?
C: ?It continually works to get to B but it never arrives.?
Human: ?So you keep getting closer to finishing your task but never actually do??
C: ?Hey, coders make a living doing that??
?}-

DmS:

Thanks for your suggestions. I read through both pages, but neither one addresses my suggestions.
The forum page does address some caching, but it didn't seem feasible for a project as big as mine.
I am looking for suggestions as to whether the suggested enhancements to my sql query will actually speed it up, or whether mysql will run the "heavy" mathmatics on all rows anyway!

-Robur

Well, not being an sql-guru myself I can't really judge the query.

What I suggested in the other thread was based on common sense only, the throught being that instead of running through all data all the time it's better to cache the queries and try there first.

What you are wondering is, as far as I understand it, more a question on how the query engine in mysql actually parses and executes the query. That I don't know enough on to address.

A stupid question though, have you tried "EXPLAIN <query>" on the two queries?
This often indicates if you are using indexes etc in an efficient way in the db-model and can give quite a lot of hints.

Other things to look at, are you using innodb or myisam?
Tables that does not need transactional support can often be a LOT faster as myisam for pure searches.

I can tell you for sure that mysql can be tuned to be insanely fast if you know what you are doing.
At work we are doing on average 2000 queries per second and peaks at 4000/sec...
(http://www.mysql.com/customers/customer.php?id=203)
Granted, it's serious hardware involved, but still...

For more and probably better optimization tips, try this: http://forums.mysql.com/list.php?115 it's mysql's own userforums for optimizing.
/D

{cell 260} {Blog}
-{" Computer:
?As a Quantum Supercomputer I take advantage of Zeno?s paradox?
C: ?Imagine a photon that must travel from A to B. The photon travels half the distance to B. From its new location it travels half the new distance. And again, it travels half that new distance.?
C: ?It continually works to get to B but it never arrives.?
Human: ?So you keep getting closer to finishing your task but never actually do??
C: ?Hey, coders make a living doing that??
?}-

Thanks!
I will check out MySql's user forums.

All my tables are MyIsam, and I do my best to make sure all my queries are optimized :-)

-Robur

also, I suggest you read up on mysql->explain and then do an explain your_query, to see if there's something that mysql itself suggests to speed your query up.

Still no replies to my thread at http://forums.mysql.com/read.php?115,91048.
Guess they're shy

Mysql itself didn't suggest anything that I could see. I already had all the necessary columns indexed.
I did find something about the BETWEEN operator, which should be able to half the number of criteria required to form the "box".

-Robur

I would store the results of each request in a column of the record. That way you can check the result column to see if it is empty and then do the calculation. If the calculation has already been done once, return the result. This means the request will be slow only the first time you make a request for a given zip-code.

.



-- not necessarily stoned... just beautiful.