Mysql query takes long time

0
votes

Hello I have table with 500k records and folowing columns:

id, id_route, id_point, lat, lng, distance, status

I want to select id_routes which are inside radius from my defined point.

Thats no problem

SELECT id_route 
FROM route_path
WHERE (((lat < 48.7210 + 2.0869) AND 
        (lat > 48.7210 - 2.0869)) AND 
       ((lng < 21.2578 + 2.0869) AND 
        (lng > 21.2578 - 2.0869))) 
GROUP BY id_route

But according PHPmyadmin it takes 0.2s. This is pretty to much since I am going to build huge query and this is just beginning.

I have also index on id_route.

Primary key is id, schema is MyISAM

EXPLAIN of SELECT:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE route_path ALL NULL NULL NULL NULL 506902 Using where; Using temporary; Using filesort

How can I reduce time, I think 500K is not su much records to make it so long? Thanks

3
mysql
what's the schema? Waht's primary key? What's index? Did you tried checking plan? – SMA
please show the Output of EXPLAIN SELECT ... – mech
That is an unusual 'radial distance' function! And performance benefits aside, GROUP BY is primarily for use with aggregate functions. – Strawberry
Primary key is id, schema is MyISAM. – rossinelo
@mech EXPLAIN of SELECT: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE route_path ALL NULL NULL NULL NULL 506902 Using where; Using temporary; Using filesort – rossinelo

3 Answers

1
votes

if queries takes longer time , and you have set the index properly then you need a powerful server to compute the query quickly !

1
votes

A 2-dimensional search is inherently slow. The tools won't tell you how to improve this particular query.

You seem to have no indexes in your table?? You should at least try INDEX(lat). that will limit the effort to a stripe of about 4 degrees (in your example). This probably includes thousands of rows. Most of them are then eliminated by checking lng, but not until after fetching all of those thousands.

So, you are tempted to try INDEX(lat, lng) only to find that it ignores lng. And perhaps it runs slower because the index is bigger.

INDEX(lat, lng, id) and using a subquery to find the ids, then doing a self-join back to the table to do the rest of the work is perhaps the simplest semi-straightforward solution. This is slightly beneficial because that is a "covering index" for the subquery, and, although you scan thousands of rows in the index, you don't have to fetch many rows in the data.

Can it be made faster? Yes. However, the complexity is beyond the space available here. See Find the nearest 10 pizza parlors. It involves InnoDB (to get index clustering), PARTITIONs (as crude 2D indexing) and modifications to the original data (to turn lat/lng into integers for PARTITION keys).

0
votes