MySQL GeoLocation query- full table scan

Question

Problem: To find list of offices for a given lat/long

MySQL Version: we're currently using 5.5.1

Table schema:

     mysql> describe delivery_office;
     +-------------------------------+--------------+------+-----+---------+-------+
     | Field                         | Type         | Null | Key | Default | Extra |
     +-------------------------------+--------------+------+-----+---------+-------+
     | id                         | bigint(20)   | NO   | PRI | NULL    |       |
     | address_1                  | varchar(255) | YES  |     | NULL    |       |
     | address_2                  | varchar(255) | YES  |     | NULL    |       |
     | address_3                  | varchar(255) | YES  |     | NULL    |       |
     | address_4                  | varchar(255) | YES  |     | NULL    |       |
     | latitude                   | double       | YES  | MUL | NULL    |       |
     | longitude                  | double       | YES  |     | NULL    |       |
     | name                       | varchar(255) | YES  |     | NULL    |       |
     | postcode                   | varchar(255) | YES  |     | NULL    |       |
     | pt_loca                    | point        | NO   | MUL | NULL    |       |
     +-------------------------------+--------------+------+-----+---------+-------+

Table indexs:

     mysql> show index from delivery_office;
     +---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+-        --------------+
     | Table               | Non_unique | Key_name     | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |         Index_comment |
     +---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+-        --------------+
     | delivery_office |          0 | PRIMARY      |            1 |  id                      | A         |        2156 |     NULL | NULL   |      | BTREE      |                 |               |
     | delivery_office |          1 | geo_index    |            1 | latitude                | A         |        1896 |     NULL | NULL   | YES  | BTREE      |                  |               |
     | delivery_office |          1 | geo_index    |            2 | longitude               | A         |        1939 |     NULL | NULL   | YES  | BTREE      |                  |               | |                 |               |
     | delivery_office |          1 | pt_loca2     |            1 | pt_loca                    | A         |        2156 |       32 | NULL   |      | SPATIAL    |               |               |
     +---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+-        --------------+

Solution: Use the Geo location query to find it.

I have used below solutions, but both result in the full table scan.

My table only has 2168 records.

Fastest Way to Find Distance Between Two Lat/Long Points

    mysql> explain SELECT  *
    ->     FROM   delivery_office
    ->     WHERE   MBRContains
    ->                     (
    ->                     LineString
    ->                             (
    ->                             Point (
    ->                             51.5177 + 1 / 111.1,
    ->        -0.0968 + 1 / ( 111.1 /COS(RADIANS(51.5177)))
    ->
    ->                                   ),
    ->                             Point (
    ->                              51.5177 - 1 / 111.1,
    ->               -0.0968 - 1 / ( 111.1 / COS(RADIANS(51.5177)))
    ->
    ->                                   )
    ->                             ),
    ->                     GeomFromText('POINT(51.5177 -0.0968)')
    ->                     );
    +----+-------------+---------------------+------------+------+-----   ----------+------+---------+------+------+----------+-------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | delivery_office | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2156 |   100.00 | NULL  |
   +----+-------------+---------------------+------------+------+------     ---------+------+---------+------+------+----------+-------+
    1 row in set, 2 warnings (0.00 sec)

https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql

    mysql> explain EXTENDED SELECT (
    ->      3959 * acos (
    ->        cos ( radians(51.3191750) )
    ->        * cos( radians( latitude ) )
    ->        * cos( radians( longitude ) - radians(-0.5632660) )
    ->        + sin ( radians(51.3191750) )
    ->        * sin( radians( latitude ) )
    ->      )
    ->    ) AS distance_in_miles, delivery_office.*
    ->  FROM delivery_office
    ->  HAVING distance_in_miles < 10
    ->  ORDER BY distance_in_miles
    ->  LIMIT 0 , 30;
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    |  1 | SIMPLE      | delivery_office | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2156 |   100.00 | Using where; Using filesort |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
    1 row in set, 2 warnings (0.00 sec)

Is there a way to fire a query for which does not result in full table scan.

Why don't you use MySQL's built-in GIS POINT datatype to store the points' coorinates, and the GIS functions (st_distance() I believe) to find the points? MySQL offers indexes on GIS data types speeding up the searches. — Shadow, Sep 09 '16 at 15:06
You can get some performance gain by adding a bounding box in a `WHERE` clause. Then follow the "latitude-longitude" tag to other MySQL solutions. — Rick James, Sep 09 '16 at 18:26
we can't use **st_distance** as it is introduced in MySQL 5.6.1 and we are using MySQL 5.5.1 @Shadow — sanjaykumar81, Sep 11 '16 at 08:23
"5.5.1" -- ".1" versions tend to be Alpha, not Beta, certainly not GA. (And it is 6 years old.) Please upgrade. — Rick James, Sep 11 '16 at 15:08

score 0 · Answer 1 · answered Sep 12 '16 at 11:20

As suggested by @RickJames I am able to optimize the query by using the solution provided here :

https://www.scribd.com/presentation/2569355/Geo-Distance-Search-with-MySQL https://blog.fedecarg.com/2009/02/08/geo-proximity-search-the-haversine-equation/

Below is the result of using this optimization, row scanning reduced from full table scan to 181 records:

     mysql> SELECT (
         ->      3959 * acos (
         ->        cos ( radians(51.3191750) )
         ->        * cos( radians( latitude ) )
         ->        * cos( radians( longitude ) - radians(-0.5632660) )
         ->        + sin ( radians(51.3191750) )
         ->        * sin( radians( latitude ) )
         ->      )
         ->    ) AS distance_in_miles
         ->  FROM delivery_office
         ->  where longitude between (-0.5632660 - 10/abs(cos(radians(51.3191750))*69)) and (-0.5632660 + 10/abs(cos(radians(51.3191750))*69)) and latitude between (51.3191750          - (10/69)) and (51.3191750 + (10/69))
         ->  HAVING distance_in_miles < 10
         ->  ORDER BY distance_in_miles
         ->  LIMIT 0 , 10;
     +--------------------+
     | distance_in_miles  |
     +--------------------+
     | 0.3381472408327969 |
     | 1.9060143092544148 |
     |  2.840055550183541 |
     | 3.2969844240913697 |
     |  3.356363831356166 |
     |  4.815726191392366 |
     |  5.134370232935941 |
     |  5.177268204112493 |
     |  5.528244889409913 |
     |  5.702281140258665 |
     +--------------------+
     10 rows in set (0.00 sec)

     mysql> explain EXTENDED SELECT (                                                                                                                                                                                        ->      3959 * acos (
         ->        cos ( radians(51.3191750) )
         ->        * cos( radians( latitude ) )
         ->        * cos( radians( longitude ) - radians(-0.5632660) )
         ->        + sin ( radians(51.3191750) )
         ->        * sin( radians( latitude ) )
         ->      )
         ->    ) AS distance_in_miles
         ->  FROM delivery_office
         ->  where longitude between (-0.5632660 - 10/abs(cos(radians(51.3191750))*69)) and (-0.5632660 + 10/abs(cos(radians(51.3191750))*69)) and latitude between (51.3191750          - (10/69)) and (51.3191750 + (10/69))
         ->  HAVING distance_in_miles < 10
         ->  ORDER BY distance_in_miles
         ->  LIMIT 0 , 10;
     +----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
     | id | select_type | table               | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                    |
     +----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
     |  1 | SIMPLE      | delivery_office | NULL       | range | geo_index     | geo_index | 18      | NULL |  181 |    11.11 | Using where; Using index; Using filesort |
     +----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
     1 row in set, 2 warnings (0.00 sec)

MySQL GeoLocation query- full table scan

1 Answers1