Searching in a database with over 3 mil. entries

Question

I'm using Geonames database for a hotel booking website. The database has two tables, one for countries, and one for cities with over 3 mil. entries. If I try to get all the cities for a specific country the query is too slow. I think is because I don't have any index defined.

The countries table has the following fields:

• iso_alpha2 (country code)
• name
• continent
• population

The cities table has the following fields:

• name
• asciiname
• alternate_names
• country

The "country" field from the cities table relates to "iso_alpha2" field in the countries table.

How can I speed up the query?

P.S. I'm using MySQL.

Answer 1

For this query, you'd only need the cities table:

select name from cities where country = 'US'

This query would benefit from an index on country.

Answer 2

You need to add an index on the field that you use in the WHERE clause (in your case it seems to be the country field).

Edit: one more thing - if you have multiple conditions in the WHERE clause you need to add an index that contains all the fields used in that clause (having separate indexes on the fields won't work). However in your case I believe that the index on the country field should do.