performance - Spatial index slowed down Query mysql -

hello have a question.

i have table time index. select statement :

select count(*) sometable          time between @starttime , @endtime       ,       st_intersects(location,@somepolygon); 

this query takes 60 seconds run. table contains more 50 million rows think okay. if add location , index query takes 90 seconds run. why slowing down ? instead of speeding ?

//update hello 4 feedback.

explain index

 id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered |   1 | simple      | q1_geo | null       | range | ort, zeit     | zeit |       5 | null | 6454092 |   100.00 | using index condition; using 

and without

id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered |  1 | simple      | q1_geo | null       | range | zeit          | zeit |       5 | null | 6454092 |   100.00 | using index condition; using 

//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 

you're hitting interesting problem in mysql indexing here. when use simple index on time query range scan on index, computes st_intersects() each row in range.

but, when add second geo index on location mysql's query planner (post explain! post table definitions!) 2 index scans , index merge.

you can't make compound index of geo , ordinary column.

another thing you'll need know speed query whether time criterion or spatial criterion more selective. 1 winnows down set of data fewer results? that's 1 want index first.

how fix this? if can break out location geo variable 2 separate columns (they might x , y, or lat , long), put time, x , y compound index, this:

where  time >= @starttime   ,  time <= @endtime   ,  x >= minx(@polygon)   ,  x <= maxx(@polygon)   ,  y >= miny(@polygon)   ,  y <= maxy(@polygon)   ,  st_intersects(location, @somepolygon) 

you'll need work out min , max functions on polygon parameters.

the point of trick allow put some if not all spatial information ordinary compound index instead of standalone geo index.