CODEWITHSUNDEEP
mysqldatabasewordpressrelation
dmnkhhn
dmnkhhn

Reputation: 1023

Looking to combine two queries

I am trying to combine two queries into one and I hope you can help me.

The query gets me list of entries (zip codes) that are within a certain distance. That works just fine. ($lat, $lng, $zip and $size are hardcoded for now).

Now I need to combine that with a search for entries from another table that match those zip codes.

This is my query:

SELECT
    source.zc_zip, 
    source.zc_location_name,
    ACOS(
         SIN(RADIANS(zc_lat)) * SIN(RADIANS($lat)) 
         + COS(RADIANS(zc_lat)) * COS(RADIANS($lat)) * COS(RADIANS(zc_lon)
         - RADIANS($lng))
         ) * 6371 AS distance
FROM zip_coordinates AS source
WHERE zc_id <> $zip
GROUP BY source.zc_zip
HAVING distance < $size
ORDER BY distance LIMIT 10;

I have already modified it to look like this because we have match when the source.zc_zip is the same as info.meta_value:

SELECT
    source.zc_zip, 
    source.zc_location_name,
    info.*,
    ACOS(
         SIN(RADIANS(zc_lat)) * SIN(RADIANS($lat)) 
         + COS(RADIANS(zc_lat)) * COS(RADIANS($lat)) * COS(RADIANS(zc_lon)
         - RADIANS($lng))
         ) * 6371 AS distance
FROM zip_coordinates AS source, wp_postmeta AS info
WHERE zc_id <> $zip AND info.meta_value = source.zc_zip
GROUP BY source.zc_zip
HAVING distance < $size
ORDER BY distance LIMIT 10;

Needless to say that this query is really slow (> 1 sec.).

Can anyone help with that? Thanks! :-)

– Dominik

Upvotes: 0

Views: 137

Answers (1)

Martin
Martin

Reputation: 1448

I asked the OP the following:

You want to combine 2 queries, you give me one (in 2 versions), but where's the second query, the one to combine with the first?

OP's reply:

  • The first one is the basic query but it lacks the information I tried to get with the second query. The second query is slow so I am looking for an alternative to the second where I combined the first query with stuff I found online. – dmnkhhn Aug 15 '11 at 18:18

$lat, $lng, $zip and $size are hardcoded for now, so adding the distance as a computed column with a stored procedure will probably not be your idea. The ORDER on a computed value obviously makes you create a temporary table with all rows and an index on the computed value. So time grows with number of rows. Take your Math's book from high school and calculate the square around your circle. Use those values to exclude 99% of the zc_lat and zc_lon from being calculated, including those limits in the WHERE.

Alternative: thinking about it last night, a better way would be programmatically: get 10 nearest living persons in the inner square of the circle. That way you don't need any calculations in the query, you calculate the limiting border in the program.

If it results in too few cases, write another query selecting in the area between the inner and the outer square, using the formula above. It will perform like a rocket: you make calculations on only some records, unless most of your customers live exactly between 9 and 11 miles when you're trying to find the 10 mile circle.

Upvotes: 1

Related Questions