From: Dario Napolitano
Date: April 2 2013 9:55am
Subject: Issue with spatial indexes not being used
List-Archive: http://lists.mysql.com/mysql/229185
Message-Id: <515AAAFF.2030002@liberologico.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="------------070108020008020800090002"
--------------070108020008020800090002
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------070108020008020800090002--