Registration form with Country, Region, City - How to manage 2 million mySQL records?

I am creating a web application using the PHP framework CodeIgniter. The app relies on correct information regarding a user's country, region and city.

I currently have the registration form with a country drop down box(populated from database), a region drop开发者_Python百科 down box (populated using ajax depending on country) and a city drop down box (jquery autocomplete using ajax depending on country and region).

My problem is the city database has over 2 million entries and is rather slow to auto complete. I have seen this type of thing done elsewhere eg. Facebook Events where the autocomplete for location is much faster.

My question is, is there any way I can speed this process up (optimise mySQL database, ajax calls) to make it quicker, or is there another way to do it using something like the Google Maps API?

Any opinions/comments/ideas appreciated.

Here are the basic definitions for my tables:

City:
countryid, char(2)
country, varchar(100)

Region:
countryid, char(2)
regionid, varchar(2)
regionName, varchar(50)

City:
cityid, int
countryid, char(2)
regionid, varchar(2)
asciName, varchar(100)
isoName, varchar(200)
population, int
long, float
lat, float

I used ISO standard lists to fill the country and region tables, and a freely available city database for the city table.

I would use www.geonames.org, which returns json for the data.

Sample usage:

http://ws.geonames.org/searchJSON?name_startsWith=San&country=US

returns:

{"totalResultsCount":12101,"geonames":[{"countryName":"United States","adminCode1":"AL","fclName":"city, ....

Pulling such data from your own database is not necessary.

How about for City table:

1. Try adding indices on countryid/regionid and asciName/isoName (whichever is used for autocomplete results)

2. Don't use LIKE "%new york%" for autocomplete query as in this form MySQL can't use VARCHAR index. You have to run it without percentage character in front of it: "new york%".

3. You could hide city input field until user selects country/region - this way you can limit autocomplete query on countryid/regionid fields (and use their indices).

4. Time your autocomplete input to only send query after 1 second (or so) elapsed since last character has been entered.