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.

4
  • 1
    Why are you storing coordinates as VARCHAR, if you later query them as numbers? Have you tried composite indices?
    – mustaccio
    Oct 7, 2016 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 Oct 7, 2016 at 14:59
  • Also, since the "numbers" are decimal/floats they wont' work at integers? Oct 7, 2016 at 15:09
  • Move to stackoverflow, then review related questions at stackoverflow.com/questions/tagged/latitude-longitude+mysql
    – Rick James
    Oct 7, 2016 at 16:53

2 Answers 2

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
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Oct 18, 2016 at 14:12
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.

3
  • For lat/long of this length would you recommend a DECIMAL? Would that still give us use of a bounding box? Oct 7, 2016 at 17:28
  • @MarcoCeppi - The length is ludicrous. See my blog for discussion of precision and datatype choices.
    – Rick James
    Oct 7, 2016 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, 2016 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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