CODEWITHSUNDEEP
mysqlcoordinates
lilsizzo
lilsizzo

Reputation: 366

slower query for searching nearby coordinates

I seem to hit slower query result for searching nearby coordinates ( for now the query is for latitude). This is a mysql query select ABS(propertyCoordinatesLat - 3.33234) as diff from tablename order by diff asc limit 0,20

is there a way to improve this besides relying on server scripting to do the sorting?

table dump.

CREATE TABLE `property` (
  `propertyID` bigint(20) NOT NULL,
  `propertyName` varchar(100) NOT NULL,
  `propertyCoordinatesLat` varchar(100) NOT NULL,
  `propertyCoordinatesLng` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `property`
--
ALTER TABLE `property`
  ADD PRIMARY KEY (`propertyID`),
  ADD KEY `propertyCoordinatesLat` (`propertyCoordinatesLat`,`propertyCoordinatesLng`),
  ADD KEY `propertyCoordinatesLat_2` (`propertyCoordinatesLat`),
  ADD KEY `propertyCoordinatesLng` (`propertyCoordinatesLng`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `property`
--
ALTER TABLE `property`
  MODIFY `propertyID` bigint(20) NOT NULL AUTO_INCREMENT;
COMMIT;

Upvotes: 0

Views: 41

Answers (1)

Schwern
Schwern

Reputation: 164829

The query is ordering by the difference between a string and a float. This odd calculation confuses and angers MySQL and results in a slow filesort.

mysql> explain select ABS(propertyCoordinatesLat - 3.33234) as diff from property order by diff 
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | property | NULL       | index | NULL          | propertyCoordinatesLat_2 | 302     | NULL |    1 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-----------------------------+

Changing propertyCoordinatesLat and propertyCoordinatesLng to a more sensible numeric type lets MySQL optimize better. No more filesort. This should perform much better.

alter table property change propertyCoordinatesLat propertyCoordinatesLat numeric(10,8) not null;
alter table property change propertyCoordinatesLng propertyCoordinatesLng numeric(11,8) not null;

mysql> explain select ABS(propertyCoordinatesLat - 3.33234) as diff from property order by propertyCoordinatesLat asc limit 0,20;
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | property | NULL       | index | NULL          | propertyCoordinatesLat_2 | 5       | NULL |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------------------+---------+------+------+----------+-------------+

If you want to get fancy, look into MySQL's spatial types. These will probably perform better, and definitely be more accurate.

Upvotes: 1

Related Questions