List:General Discussion« Previous MessageNext Message »
From:Dario Napolitano Date:April 2 2013 9:55am
Subject:Issue with spatial indexes not being used
View as plain text  
I'm being puzzled by a strange issue that seems to affect spatial 
indexes on a Linux installation.
That database includes a couple MyISAM tables with a SPATIAL index - 
namely the locations of some shops and the locations of several ZIP 
codes. They are uses in a query to find shops near certain locations.
On the production server (Linux based) the SPATIAL index never gets 
used. On other machines, instead, with the same tables and data, the 
index works and speeds up the query considerably.

Does anyone know what could cause this?

Thanks for any help.

Details:

Here's the query:

SELECT DISTINCT(CAP)
     from CAP_GEO cap force index(IDX_CAP_GEO)
     JOIN SHOPS_GEO sg  force index(IDX_SHOP_GEO)
     ON (MBRContains(RangeBox(sg.location, 0.01904761977493763), 
cap.location))
     JOIN SHOPS s ON (s.id=sg.id)
     WHERE s.FK_MASS_RETAILER= 38;

CAP_GEO is a table with ZIP codes and their approximate location, while 
SHOPS_GEO is a table with shops and their location.
RangeBox is a function that returns a box centered on a location with a 
given distance.

The query tries to locate all the ZIP codes within the given distance 
from a shop of a given chain.

Here's the production server's details:
version 	5.5.25
version_comment 	MySQL Community Server (GPL) by Remi
version_compile_machine 	x86_64
version_compile_os 	Linux


And here's the EXPLAIN on the query, which runs with a full scan:

SIMPLE 	cap 	ALL 	IDX_CAP_GEO 	
	
	
	4508 	Using temporary
SIMPLE 	sg 	ALL 	
	
	
	
	28879 	Using where; Distinct; Using join buffer
SIMPLE 	s 	eq_ref 	PRIMARY,FKSHOPS73055 	PRIMARY 	4 	db.sg.ID 	1 	Using 
where; Distinct



Another machine:
version 	5.5.25
version_comment 	MySQL Community Server (GPL)
version_compile_machine 	x86
version_compile_os 	Win32


Here the index gets used ('Range checked')

SIMPLE 	sg 	ALL 	
	
	
	
	28879 	Using temporary
SIMPLE 	cap 	ALL 	IDX_CAP_GEO 	
	
	
	4508 	Range checked for each record (index map: 0x2)
SIMPLE 	s 	eq_ref 	PRIMARY,FKSHOPS73055 	PRIMARY 	4 	db.sg.ID 	1 	Using 
where; Distinct



Yet another machine, with an older MySQL:

version 	5.1.67-0ubuntu0.10.04.1
version_comment 	(Ubuntu)
version_compile_machine 	i486
version_compile_os 	debian-linux-gnu


Same result:

SIMPLE 	sg 	ALL 	
	
	
	
	28879 	Using temporary
SIMPLE 	cap 	ALL 	IDX_CAP_GEO 	
	
	
	4508 	Range checked for each record (index map: 0x2)
SIMPLE 	s 	eq_ref 	PRIMARY,FKSHOPS73055 	PRIMARY 	4 	db.sg.ID 	1 	Using 
where; Distinct


Thread
Issue with spatial indexes not being usedDario Napolitano2 Apr