How to change this MySQL query to SELECT from one table instead of two?

Question

I have a table "geo_locations" that looks like this:

country | city | postalCode | latitude | longitude | metroCode | areaCode ------------------------------------------------------------------------- US | Chadler | 85226 | 33.2769 | -111.9444 | 753 | 480 more records... 

And the "users" table, which looks like this:

  user_id | sex | dob | country | region | city | zip | latitude | longitude | email | username | password -------------------------------------------------------------------------------------------------------------------------------------- 1 | m | 1987-05-14 | US | NY | Flushing | 11398 | 40.7723 | -73.8722 | [e-mail] | HiBye99 | 54524sAS%ASa2?&^312 more records... 

My application requires me to recommend users to other users, meetings, friends, etc. I must recommend the user to other users who are within their area. It doesn't make sense to recommend someone from Bejing to someone in New York, for example.

For this, I use the following sql:

  SELECT postalCode, latitude, longitude, ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius AS D FROM ( SELECT postalCode, latitude, longitude FROM geo_locations WHERE latitude > $min_lat AND latitude < $max_lat AND longitude > $min_lon AND longitude < $max_lon ) AS FirstCut WHERE ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius < $rad ORDER BY D 

Prior to this sql, some fancy calculations were performed for the variables $ lat, $ lon, $ rad, $ radius, $ min_lat, $ max_lat, $ min_lon and $ max_lon. The full code can be here, where I took the code from:

 http://www.movable-type.co.uk/scripts/latlong-db.html 

In any case, what this sql returns in my code is the totality of all the "postal codes" that are close to the user's domain. So I use these zip codes to create another SQL query that usually looks crazy like this (I wouldn't ask a question because it was so huge):

http://jsfiddle.net/dgmHa/

And after starting in the users table, I get a list of recommended users.

Question:

Now, as you can see, I have latitude and longitude columns in the users table. Therefore, there is no need to select anything from the geo_locations table. How can I modify my query so that I can get all my recommended users directly from the "users" table in one query?

Also my way to do it all in a nightmare performance, is there a better way around this?

Answer 1

To answer your first question, you should write a query like:

  SELECT user_id, zip, latitude, longitude, (ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius) AS SearchRadius FROM users WHERE (latitude > $min_lat AND latitude < $max_lat) AND (longitude > $min_lon AND longitude < $max_lon) AND (ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius) < $rad ORDER BY SearchRadius 

i.e. you can effectively ignore the geo_locations table and just select all the same columns from users.

As for the second part, to be completely honest, the best way to solve this is to do some testing. Fill in the user table with a lot of information and determine how long the request will take. Then duplicate the number of records and retest. This way you can see the effect of more data on your query.

Alternatives include simply selecting the source data from the database and performing the calculations in code. Again, you will have to test to see the performance benefits / negatives.