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.