Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.