Best way to quert a DB with 53,000 entries?

[phaedrusGhost]

Hi all,

I have a cities table in MySQL with 3 rows in it.

Table 'cities'

row 1 'city'
row 2 'statename'
row 3 'stateabbreviation'

With 52,593 cities in the DB.

I would like to create an auto search form so that while typing a city in it will show possible results however, I am concerned about the speed of this and more importantly the proper approach.

I would really like to connect to an API that has all this info currently stored so that I can not only have the users find a city but it will find addresses as well. In addition, it will stay up to date.

As always, I appreciate the feedback and look forward to your suggestions.

[rch1231]

This is not exactly answering your question but I have found it to be an easier method. I use a free zipcode table from the Census I downloaded from http://federalgovernmentzipcodes.us/.

There is a CSV format, xls file and Yes a MySQL dump (The SQL code to do table create and import for you). If on your page ask for the Zip (like a lot of sites do now) instead of the City and State you are dealing with a numerical index instead of a string. You can quickly narrow the list and verify it with the user. Table includes Longitude and Latitude (great for google maps), county, and some other handy data. Table includes various city spellings however the main unique zip code record is indicated by the zprimary='Yes'. I used the data to generate a separate table with zipcode as the primary key and only the zprimary = 'Yes' records and there is really no need to verify the city/State information.

Hope this helps.

[griswolf]

Added note: You may not want to keep both the state name and the state abbreviation in the `cities` table since there is a one to one relationship between them. If you actually need both, perhaps `cities` should have a foreign key into a `states` table that holds 51 or a few more rows (Washington DC, Puerto Rico,...) with two columns. I do like rch1231's idea.