mysql - Can I make this SQL query more efficient? -

i have sql query works fine, feel there must more efficient way of writing it. joining 2 tables , b contain coordinates, comparing distances (in metres) between each coordinate. sum/count number of coordinates in table b within set distances of coordinates in table , output result:

select a.name,        sum(case when st_geodesiclengthwgs84(st_setsrid(st_linestring(a.lat, a.lon, b.lat, b.lon),4326)) < 10.0 1 else 0 end) 10mcount,        sum(case when st_geodesiclengthwgs84(st_setsrid(st_linestring(a.lat, a.lon, b.lat, b.lon),4326)) < 50.0 1 else 0 end) 50mcount,        sum(case when st_geodesiclengthwgs84(st_setsrid(st_linestring(a.lat, a.lon, b.lat, b.lon),4326)) < 1000.0 1 else 0 end) 1000mcount  join      b group a.name order 1000mcount desc limit 10; 

i feel there must way call st_geodesiclengthwgs84(st_setsrid(st_linestring(a.lat, a.lon, b.lat, b.lon), 4326)) once, result , increment each 10m, 50m , 1000m count.

any ideas? thanks.

try prequerying, data still requiring results go through every record.

select       prequery.name,       sum(case when prequery.geode < 10.0 1 else 0 end) 10mcount,       sum(case when prequery.geode < 50.0 1 else 0 end) 50mcount,       sum(case when prequery.geode < 1000.0 1 else 0 end) 1000mcount           ( select                a.name,               st_geodesiclengthwgs84( st_setsrid( st_linestring(a.lat, a.lon, b.lat, b.lon),4326)) geode                           join b                    (you missing join 'on' clause... how related) ) prequery    group        prequery.name    order        1000mcount desc    limit 10;