Display ALL countries in an autocomplete form

Question

First time posting on here because Google is yielding no results!

So, I have a website that is based around travelling and locations. Everytime someone enters content into the site, they select a location and that content then has lat and long, country, etc.

The issue I face is that I have a DB of all the "cities and areas" of the world and there are a good 3.5 million records in the database I believe.

My question to you is how would you guys recommend doing a 1 field autocomplete form for all the cities? I don't need advice on the autocomplete form itself, I need advice on HOW and WHERE I should be storing the data... text files? SQL? Up until now, I have been using SQL but I don't know how it should be done. Would an AJAX autoloader be able to handle it if I only returned 100 records or so? Should all the results be preloaded?

Thanks for your help guys!

EDIT: I have actually found another way to do it. I found this awesome little plugin to integrate Google Maps with it

http://xilinus.com/jquery-addresspicker/demos/index.html

Fantastic.

Answer 1

Benny

I have a few thoughts here:

1. since you don't know whether a user will enter the english or local (native) name, each city record in your database should have both. Make sure to index these fields.

2. Do not do auto-complete until you have a minimum number of characters. Otherwise, you will match way too many rows in your table. For example, assuming an even distribution of english characters (26), then at 3.5 million records you would statistically get thar = he following matches per character:

1 char = 135k
2 char = 5.2k
3 char = 200
4 char = 8

1. If you are using MySQL you will want to use the LIKE specifier.

There are much more advance methods for predictive matching, but this should be a good start.