My server is on a shared host which is running MySql version 5.5.30-30.2 - Percona Server (GPL), Release rel30.2, Revision 509 (taken from phpmyadmin).
I have a table containing 10,023 records defined like this
CREATE TABLE IF NOT EXISTS `poi` (
`id_poi` INT NOT NULL AUTO_INCREMENT ,
`Latitude` DECIMAL(20,17) NOT NULL ,
`Longitude` DECIMAL(20,17) NOT NULL ,
`LatLng` GEOMETRY NOT NULL ,
`Name` VARCHAR(80) NOT NULL ,
...
PRIMARY KEY (`id_poi`) ,
SPATIAL INDEX `position_index` (`LatLng` ASC) )
ENGINE = MyISAM
I have implemented a PointInPolygon user defined function which I found here (http://forums.mysql.com/read.php?23,286574,286574) and call it within a stored procedure.
This stored procedure takes a polygon (representing a buffer around an itinerary) and, should, return records which are contained within the polygon.
The stored procedure does a "simple" select, joining to other tables for output -
select p.id_poi, p.Latitude, p.Longitude, p.Name, p.Adresse1, p.Adresse2,
p.CodePostale, p.Commune,
p.contract_type, p.contract_active, p.position_aprox, p.media_dir,
p.ot_managed, (select id_poi_ot from poi_ot_managed as ot
where p.id_poi=ot.id_poi) as ot_ref,
(select if(isnull(Name), null, concat(Name, "<br/>", Commune))
from poi as ot join poi_ot_managed as otm on ot.id_poi=otm.id_poi_ot
where otm.id_poi=p.id_poi) as ot_structure,
group_concat(concat(g.Name, ' - ', c.Name) order by h.principal desc,
g.Name, c.id_poi_category separator ';') as Cats,
group_concat(concat(g.Name_en, ' - ', c.Name_en) order by h.principal desc,
g.Name, c.id_poi_category separator ';') as Cats_en,
group_concat(c.Marker_basic order by h.principal desc, g.Name,
c.id_poi_category separator ';') as icons
from poi as p join poi_has_categories as h on p.id_poi = h.id_poi
join poi_category as c on c.id_poi_category=h.id_poi_category
join poi_cat_group as g on g.id_poi_cat_group=c.id_poi_cat_group
where MBRContains(poly,p.LatLng) and PointInPolygon(p.LatLng, poly)
group by p.id_poi
order by p.contract_type desc;
This works great until I try to do a large search - itinerary spanning all of France. I send a polygon containing 2384 points. The stored procedure takes 197 seconds (3 minutes and 17 seconds) = far too long.
Can anyone advise me as to how to optimise this / speed it up ? At this time, my php script is being timed out by the apache server and I do not have any control over the apache installation.
I hope someone will be able to help here.