We have quite a large table in our database which looks something like this:
CREATE TABLE `nests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pokemon_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `latitude` varchar(64) NOT NULL, `longitude` varchar(64) NOT NULL, `is_cluster` int(11) NOT NULL, `is_repeater` int(11) NOT NULL, `status` int(11) DEFAULT NULL, `spawn_type` int(11) DEFAULT NULL, `current_species` int(11) DEFAULT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`), KEY `current_species` (`current_species`), KEY `spawn_type` (`spawn_type`), KEY `status` (`status`), KEY `latitude` (`latitude`), KEY `pokemon_id` (`pokemon_id`) ) ENGINE=InnoDB AUTO_INCREMENT=76214 DEFAULT CHARSET=latin1;
This table basically stores user submitted data and is polled aggressively to find results within a bounding box. Here's an example of a few of the queries which run against this table.
SELECT `id`, `current_species`, `status`, `spawn_type`, `latitude`, `longitude` FROM `nests` WHERE latitude <= 50.953560763747 AND latitude >= 50.898161073089 AND longitude <= 7.0303058624268 AND longitude >= 6.8912601470947 AND `spawn_type` IN (1, 2, 3, 0) AND `status` IN (1, 2) LIMIT 201;
The table, today, has 76,000 rows and when running an
EXPLAIN for that query it seems to narrow by very little and keys off of status.
id: 1 select_type: SIMPLE table: nests type: range possible_keys: spawn_type,status,latitude key: status key_len: 5 ref: NULL rows: 54288 Extra: Using index condition; Using where 1 row in set (0.00 sec)
We have a few ideas, but since rolling out this query we've had to create A LOT of MySQL read slaves because of how long it takes to run these queries.
The two things I've looked into so far are to create a new index (and remove a bunch more) where we take
latitude, remove the decimal and flatten to the first 5 ints. So, a
50953 and use this column to key off of.
The second thing I've started looking into is GEOSPATIAL
POINT type and key off of that. However, I understand that would require us changing from InnoDB to MyISAM and I'm not sure if it'll help as I have very little experience in GEOSPATIAL column/data.
The final thought I had on this was to break out the lat/long data into it's own MyISAM table with an
id and a
POINT, then create a foreign key relation to the
nests table. Thereby querying the geospatial data and inner join that onto nests for final filters.