2

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 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.

  • 1
    Why are you storing coordinates as VARCHAR, if you later query them as numbers? Have you tried composite indices? – mustaccio Oct 7 '16 at 14:55
  • I have not tried composite indices, as I'm not 100% sure how to utilize them. I also didn't realize the field type was VARCHAR until you told me. This is probably because I can't see the forest for the trees I've been staring at this so long – Marco Ceppi Oct 7 '16 at 14:59
  • Also, since the "numbers" are decimal/floats they wont' work at integers? – Marco Ceppi Oct 7 '16 at 15:09
  • Move to stackoverflow, then review related questions at stackoverflow.com/questions/tagged/latitude-longitude+mysql – Rick James Oct 7 '16 at 16:53
4

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` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 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` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 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` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 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` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 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` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)
1

Do not use VARCHAR for numeric values that you will compare against. And it prevented the use of the bounding box.

Do not index flags; such indexes are unlikely to be used. (I am surprised that it used INDEX(status); sounds like a bug.)

Do use TINYINT UNSIGNED for flags, not the 4-byte INT.

Do use ORDER BY when using LIMIT, unless you really don't care which rows you get.

No "composite" index would be helpful for that query.

  • For lat/long of this length would you recommend a DECIMAL? Would that still give us use of a bounding box? – Marco Ceppi Oct 7 '16 at 17:28
  • @MarcoCeppi - The length is ludicrous. See my blog for discussion of precision and datatype choices. – Rick James Oct 7 '16 at 17:53
  • 1
    Bounding box will not work for VARCHAR, but will work for any numeric datatype. See mysql.rjweb.org/doc.php/latlng#representation_choices for various precision choices. – Rick James Oct 8 '16 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.