Re-posting it from stackoverflow as per their suggestion. Link to the thread in stackoverflow https://stackoverflow.com/questions/53182385/optimizing-mysql-query-to-select-all-points-with-in-polygon-using-spatial-indexe

Firstly, I admit that my experience with spatial functions is very minimal. I have a table in MySQL with 20 fields and 23549187 records that contain geographical data. One of the fields is 'point' which is of point data type and has spatial index on it. I have a query that selects all points within a polygon which looks like this,

select * from `table_name` where ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((151.186 -23.497,151.207 -23.505,151.178 -23.496,151.174 -23.49800000000001,151.176 -23.496,151.179 -23.49500000000002,151.186 -23.497))'), `point`)

This works well as the polygon is small. However, if the polygon gets massive, the execution times gets really slow and the slowest query until now ran for 15 mins. Adding the index had really helped to bring it down to 15 mins which otherwise would have taken close to an hour. Is there anything I can do here for further improvement. This query will be run by a PHP script that runs as a daemon and I am worried if this slow queries will bring the MySQL server down.

show create table;

CREATE TABLE `table_name` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `lat` float(12,6) DEFAULT NULL, `long` float(12,6) DEFAULT NULL, `point` point NOT NULL, PRIMARY KEY (`id`), KEY `lat` (`lat`,`long`), SPATIAL KEY `sp_index` (`point`) ) ENGINE=MyISAM AUTO_INCREMENT=47222773 DEFAULT CHARSET=utf8mb4 There are few more fields that I am not supposed to disclose it here however the filter won

Explain sql output for the slow query:

+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | table_name | ALL | NULL | NULL | NULL | NULL | 23549187 | Using where | +----+-------------+--------+------+---------------+------+---------+------+----------+-------------+

Explain sql output for query with smaller polygons,

+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | table_name | range | sp_index | sp_index | 34 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+

Looks like the biggest polygon does not use the index.

  • 1
    This is the behavior I would expect -- highly selective indexes work better than less selective indexes, until using the index is actually slower. You may be able to gain performance by spatially defragmenting the table, but with 23+ million rows, you might not get too far in that regard. – Vince Nov 12 '18 at 0:30
  • Cross-posted as stackoverflow.com/questions/53182385 – PolyGeo Nov 12 '18 at 1:03
  • Do you need the 20 fields to be fetched for every record ? and does any of the fields contain a large amount of data apart from Point field ? – Shiko Nov 12 '18 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.