performance - Start and end on (near) the route in mysql -

i working on searching system should detect if start , end point on (near 50 km) route. have many routes stored in mysql db points [300k rows].

structure  id [primary] | id_route | id_point | lat_lng_point (spatial index)  1              1          1          [geometry - 25 b] 2              1          2          [geometry - 25 b] 3              1          3          [geometry - 25 b] 4              1          4          [geometry - 25 b] 5              2          1          [geometry - 25 b] 6              2          2          [geometry - 25 b] ...            ...        ...        ...     

question how effectivelly select routes (route_id) on (or near 50 km) start , end points is?

i have tryed union [in example] (or inner join) works, query takes around 0.4s much. any idea how optimize?

select *          (             (                 select distinct(id_route)                 route_path2                 st_contains( st_makeenvelope(                                     point(($lng_start+(50/111)), ($lat_start+(50/111))),                                     point(($lng_start-(50/111)), ($lat_start-(50/111)))                                  ), route_path2.lat_lng_point )             )         union             (                 select distinct(id_route)                 route_path2                 st_contains( st_makeenvelope(                                     point(($lng_end+(50/111)), ($lat_end+(50/111))),                                     point(($lng_end-(50/111)), ($lat_end-(50/111)))                                  ), route_path2.lat_lng_point )             )           ) t group id_route having count(*) >= 2  

edit:

i made optimization based on @djeramon advice ano 0.06s dont know best can achieve , if have 50m rows:)

create temporary table starts_on_route select distinct id_route route_path2 st_contains( st_makeenvelope(                     point((17.1077+(50/111)), (48.1486+(50/111))),                     point((17.1077-(50/111)), (48.1486-(50/111)))                  ), route_path2.lat_lng_point );  create index starts_on_route_inx on starts_on_route(id_route);  select distinct route_path2.id_route route_path2 left join starts_on_route  on route_path2.id_route = starts_on_route.id_route st_contains( st_makeenvelope(     point((18.7408+(50/111)), (49.2194+(50/111))),     point((18.7408-(50/111)), (49.2194-(50/111))) ), lat_lng_point ) , route_path2.id_route = starts_on_route.id_route; 

currently you're running query twice on entire route table. try run first subquery determine routes valid start point , run second subquery on relevant routes. should safe approximately 50% of processing time.

one approach using temporary table store result of first query. however, need watch out created overhead , it's idea create index it. few more details refer http://blog.endpoint.com/2015/02/temporary-tables-in-sql-query.html