Point in Polygon woes
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.