Tell me more ×
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

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

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.