MySQL - How to speed up search with using geolocation data?

Question

I have a table with cars and I am trying to find all cars in a city within a radius. Here's a query I am using for this:

SELECT cars.*,
 69.0 * HAVERSINE(cars.latitude,cars.longitude, 32.7802618, -96.80097810000001) AS distance
FROM cars
WHERE cars.latitude BETWEEN 32.7802618 - (100.0 / 69.0)
 AND 32.7802618 + (100.0 / 69.0)
 AND cars.longitude BETWEEN -96.80097810000001 - (100.0 / (69.0 * COS(RADIANS(32.7802618))))
 AND -96.80097810000001 + (100.0 / (69.0 * COS(RADIANS(32.7802618))))
 AND cars.pick_up_available = 0
ORDER BY distances

I am testing this query, but it seems to be working (it finds cars within a 100-miles radius from a city from latitude and longitude coordinations). There's a downside, though - the query is quite slow. In the table is 300,000 records and when I run this query, it returns 5,400 records and it took 4.5 seconds.

I've tried to speed it up by adding indexes, like this: CREATE INDEX idx_latitude_longitude ON cars (latitude, longitude), but it didn't help (it takes 4.5 seconds).

    show index from cars;
        +----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        | Table  | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
        +----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
        | cars   |          0 | PRIMARY                |            1 | id          | A         |      253096 |     NULL | NULL   |      | BTREE      |         |               |
        | cars   |          1 | idx_latitude_longitude |            1 | latitude    | A         |       84365 |     NULL | NULL   | YES  | BTREE      |         |               |
        | cars   |          1 | idx_latitude_longitude |            2 | longitude   | A         |       84365 |     NULL | NULL   | YES  | BTREE      |         |               |

        3 rows in set (0.13 sec)

Thank you guys in advance.

Answer 1

SELECT cars.*,
 69.0 * HAVERSINE(cars.latitude,cars.longitude, 32.7802618, -96.80097810000001) AS distance
FROM cars
WHERE cars.pick_up_available = 0
 AND 32.7802618 + (100.0 / 69.0)
 AND cars.longitude BETWEEN -96.80097810000001 - (100.0 / (69.0 * COS(RADIANS(32.7802618))))
 AND -96.80097810000001 + (100.0 / (69.0 * COS(RADIANS(32.7802618))))
 AND cars.latitude BETWEEN 32.7802618 - (100.0 / 69.0)
ORDER BY distances

It's not so big change but it's limited maths operation's on data.

Answer 2

slow queries have many reasons. not using index, sorting overhead, wrong table structure.... my guess your query can't using index or sorting overhead is too big. but it just my guess. can you show 'explain select' and 'SET PROFILING=1; query; show profiles;' result?