Tweaking indexes for tables with "geographical" data 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 &lt;= 50.953560763747
  AND latitude &gt;= 50.898161073089
  AND longitude &lt;= 7.0303058624268
  AND longitude &gt;= 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 latitude of 50.953560763747 becomes 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.
A couple of things to mention about this query:


There is no need to use MyISAM.  InnoDB in MySQL 5.7+ supports geospatial types, and rtree indexes.
Independent of the above, this is a key use case for composite indexes.  Possibly: ALTER TABLE nests ADD INDEX s_s_l (status, spawn_type, latitude).  You will not be able to effectively use latitude and longitude in the same index, as the remainder of the composite index can not be used after a ranged condition.




It looks like there is some doubt in the comments that this works.  Let me show the EXPLAIN output:

ALTER TABLE nests DROP INDEX status, DROP INDEX latitude, DROP INDEX spawn_type;
ALTER TABLE nests CHANGE latitude latitude DECIMAL(10, 8) NOT NULL;
ALTER TABLE nests CHANGE longitude longitude DECIMAL(10, 8) NOT NULL;
ALTER TABLE nests ADD INDEX s_s_l (status, spawn_type, latitude);

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "s_s_l"
      ],
      "key": "s_s_l",
      "used_key_parts": [
        "status",       # All parts of the key
        "spawn_type",   # are used, including
        "latitude"      # the last part.
      ],
      "key_length": "15",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "11.11",
      "index_condition": "((`test`.`nests`.`latitude` &lt;= 50.953560763747) and (`test`.`nests`.`latitude` &gt;= 50.898161073089) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "19.03",
        "eval_cost": "0.18",
        "prefix_cost": "19.21",
        "data_read_per_join": "49"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ],
      "attached_condition": "((`test`.`nests`.`longitude` &lt;= 7.0303058624268) and (`test`.`nests`.`longitude` &gt;= 6.8912601470947))"
    }
  }
}


An index on (s,s,l,l) only uses the index for (s,s,l), but applies Index Condition Pushdown for the remaining (l):

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "s_s_l_l"
      ],
      "key": "s_s_l_l",
      "used_key_parts": [
        "status",
        "spawn_type",
        "latitude"  # last part of key not used
      ],
      "key_length": "20",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "11.11",
      "index_condition": "((`test`.`nests`.`latitude` &lt;= 50.953560763747) and (`test`.`nests`.`latitude` &gt;= 50.898161073089) and (`test`.`nests`.`longitude` &lt;= 7.0303058624268) and (`test`.`nests`.`longitude` &gt;= 6.8912601470947) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "19.03",
        "eval_cost": "0.18",
        "prefix_cost": "19.21",
        "data_read_per_join": "49"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)


And l_s_s is only effective for filtering (l), with ICP again used for (s,s):

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "l_s_s"
      ],
      "key": "l_s_s",
      "used_key_parts": [
        "latitude"  # Only first part of key used
      ],
      "key_length": "15",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "0.89",
      "index_condition": "((`test`.`nests`.`latitude` &lt;= 50.953560763747) and (`test`.`nests`.`latitude` &gt;= 50.898161073089) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "12.20",
        "eval_cost": "0.01",
        "prefix_cost": "12.21",
        "data_read_per_join": "3"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ],
      "attached_condition": "((`test`.`nests`.`longitude` &lt;= 7.0303058624268) and (`test`.`nests`.`longitude` &gt;= 6.8912601470947))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)