I'm trying a little experiment at pushing a data set which is not geo-spatial but fits it quite well and am finding the results somewhat unsettling. The data set is genomic data e.g. the Human Genome where we have a region of DNA where elements like genes occupy specific start and stop coordinates (our X axis). We have multiple regions of DNA (chromosomes) which occupy the Y axis. The goal is to bring back all the items which intersect two X coordinates along a single Y coordinate e.g. LineString(START 1, END 2).
The theory seemed sound so I pushed it into an existing MySQL based genome project and came up with a table structure like:
CREATE TABLE `spatial_feature` (
`spatial_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`external_id` int(10) unsigned NOT NULL,
`external_type` int(3) unsigned NOT NULL,
`location` geometry NOT NULL,
PRIMARY KEY (`spatial_feature_id`),
SPATIAL KEY `sf_location_idx` (`location`)
) ENGINE=MyISAM;
external_id represents the identifier of the entity we have encoded into this table & external_type encodes the source of this. Everything looked good and I pushed in some preliminary data (30,000 rows) which seemed to work well. When this increased past the 3 million row mark MySQL refused to use the spatial index and was slower when it was forced to use it (40 seconds vs. 5 seconds using a full table scan). When more data was added the index started to be used but the performance penalty persisted. Forcing the index off brought the query down to 8 seconds. The query I'm using looks like:
select count(*)
from spatial_feature
where MBRIntersects(GeomFromText('LineString(7420023 1, 7420023 1)'), location);
The data going into this is be very dense along the Y dimensions (think of it like you've recorded the position of every building, telephone box, post box and pigeon on a very long