Optimizing a Query very slow due to the having condition

giansi 0
Viewed 36

I'm trying to optimize the following query which is very very slow if I add the group by and the having condition in the end. Is there a different way to process it?

    set @lat = (select latitude  from t_zip where code in (  SELECT id_house FROM hp0.t_house_config where id_house=39));
    set @lon = (select latitude  from t_zip where code in (  SELECT id_house FROM hp0.t_house_config where id_house=39));
    
    SELECT sp2.rkd_property_zip,
                sp1.code,
                   round( (6371.393 * ACOS(COS(RADIANS(@lat)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(@lon)) + SIN(RADIANS(@lat)) * SIN(RADIANS(latitude)))),0) as distance
            FROM
                hp0.t_zip sp1

inner join t_rdk sp2 on sp1.code = sp2.rkd_property_zip
group by distance
having distance<10
mysql
3 Answers

Not good at math with respect to lat/long computations, but if you try to reverse it out some to limit your scope, you could apply a where clause. Say for simple purposes your lat is 10, long is 20 (I know, these are not true lat/long, but go with it). If you want a distance of 12, compute out what is the farthest distance allowed on the lat side, 10+12 = 22, but 10-12 = -2, so your WHERE clause would restrict down to a LAT >= -2 AND LAT <= 22.

Apply similar logic on the LONG and you now have a concise area. So you are not querying every house in the world, when you might be looking at an area even in a larger town/city like New York City, or a small town like Mexico, NY (blink and you miss it driving on the highway).

Now, having an index on your table for both lat and long, your query should be easily optimizable. I am sure there might also be some geospatial functions, but again, never needed lat/long work much.

DRapp0

The query will be slow because it just test every row in the table.

For speed, see Find Nearest

SELECT id_house FROM ... where id_house=39

Why run the query if you already know the id???

Rick James0

Hard to really tell without having a good understanding of all the tables however I would suggest two things:

  1. Do not use the calculation on both Select clause and Group By clause as you are making the process very expensive. Many ways to skin this cat, I will leave that homework to you
  2. Although I have not done the math myself, I find it hard to believe that you really want to group by using a math equation, if you really want to group by this value just use the variables that you already have to do the grouping, example something like Group By latitude, longitude
KoU_warch0
Related