Spatial index slowed down Query mysql

Question

Hello i have a a question.

I have a Table where the time is an index. My select statement look like this :

select count(*) from sometable
   where
      time between @startTime and @endTime
      and
      st_intersects(location,@somePolygon);

this Query takes 60 seconds to run. The table contains more then 50 million rows so i think it is okay. But now if i add location as and Index the Query takes 90 seconds to run. Why is it slowing down ? instead of speeding up ?

//Update Hello thanks four the feedback.

Explain with Index

 id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra
  1 | SIMPLE      | q1_geo | NULL       | range | ORT, Zeit     | Zeit |       5 | NULL | 6454092 |   100.00 | Using index condition; Using where

and without

id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra
 1 | SIMPLE      | q1_geo | NULL       | range | Zeit          | Zeit |       5 | NULL | 6454092 |   100.00 | Using index condition; Using where

//Update

Version : '5.7.5-m15' Engien : MyISAM

Table, Create Table

CREATE TABLE `q1_geo` (
  `ID` int(11) NOT NULL,
  `ZEIT` datetime NOT NULL,
  `R_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ORT` point NOT NULL,
  PRIMARY KEY (`ID`,`ZEIT`,`R_ID`),
  KEY `Zeit` (`ZEIT`),
  KEY `Ort` (`ORT`(25))
) ENGINE=MyISAM AUTO_INCREMENT=842057641 DEFAULT CHARSET=latin1

Please post the EXPLAIN results of your query with and without the location index. — Paul Spiegel
– Paul Spiegel, Commented Jan 16, 2016 at 13:28
What other important queries do you have? A solution for this query may hurt others. — Rick James
– Rick James, Commented Jan 16, 2016 at 16:05
Potentially relevant here are your server version (SELECT @@VERSION;) and storage engine ENGINE=MyISAM || ENGINE=InnoDB in the table definition. Add the output of SHOW CREATE TABLE ... please. — Michael - sqlbot
– Michael - sqlbot, Commented Jan 17, 2016 at 0:15

O. Jones · Accepted Answer · 2016-01-16 16:31:21Z

2

You're hitting an interesting problem in MySQL indexing here. When you use the simple index on time your query does a range scan on that index, then it computes st_intersects() for each row in that range.

But, when you add a second geo index on location MySQL's query planner probably (post your EXPLAIN! Post your table definitions!) does two index scans and then an index merge.

You can't make a compound index of a geo and an ordinary column.

Another thing you'll need to know to speed up this query is whether your time criterion or your spatial criterion is more selective. Which one winnows down your set of data to fewer results? That's the one you want to index first.

How do you fix this? If you can break out your location geo variable into two separate columns (they might be x and y, or lat and long), then put the time, x and y into a compound index, then do something like this:

WHERE  time >= @startTime
  AND  time <= @endTime
  AND  x >= MinX(@polygon)
  AND  x <= MaxX(@polygon)
  AND  y >= MinY(@polygon)
  AND  y <= MaxY(@polygon)
  AND  st_intersects(location, @somePolygon)

You'll need to work out the Min and Max functions on your polygon parameters.

The point of this trick is to allow you to put some if not all your spatial information into an ordinary compound index instead of into a standalone geo index.

edited Jan 16, 2016 at 16:31

answered Jan 16, 2016 at 14:29

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Montezuma Over a year ago

I put Explain statements in the Question :) Thanks for the answers

Collectives™ on Stack Overflow

Spatial index slowed down Query mysql

Table, Create Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table, Create Table

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related