Optimizing searches for big MySQL table

Question

I'm working with a MariaDB (MySQL) table which mainly contains information about the whole world cities, their latitude/longitude and the country code (2 characters) where the city is. The table is so big, over 2.5 milion rows.

show columns from Cities;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| city    | varchar(255) | YES  |     | NULL    |                |
| lat     | float        | NO   |     | NULL    |                |
| lon     | float        | NO   |     | NULL    |                |
| country | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

I want to implement a city searcher, so I have to optimize the SELECTS, not the INSERTS or UPDATES (it will be always the same information).

I thought that I should:

1. create an index (by city? by city and country?)
2. create partitions (by country?)

Should I do both tasks? If so... How could I do them? Could anyone give me several advices? I'm a little bit lost.

PS. I tryied this to create and index by city and country (I don't know if I am doing it well...):

CREATE INDEX idx_cities ON Cities(city (30), country (2));

Answer 1

Do not use "prefix indexing". Simply use INDEX(city, country) This will work very well for either of these:

WHERE city = 'London'   -- 26 results, half in the US
WHERE city = 'London' AND country = 'CA'  -- one result

Do not use Partitions. The table is too small, and there is no performance benefit.

Since there are only 2.5M rows, use id MEDIUMINT UNSIGNED to save 2.5MB.

What other queries will you have? If you need to "find the 10 nearest cities to a given lat/lng", then see this.

Your table, including index(es), might be only 300MB.