Hi All

At present i am working with a database that contains all countries and their cities. My database is in mysql. The city table contains 3079703 cities.

When a user selects a country i need to populate the city dropdown for that country. I use Ajax technology to retrieve city from database based on the selected country.

But I am facing a problem with the speed as it takes a long time to retrieve city data and return generated dropdown control from server using php.

I have already tried to use filebased caching (transferring all the city data into separate files for each state and country) and then tried to load from these files but didn't gain any significant speed improvement. I also then tried using indexing in city table on country code and state code but it still takes time to load.

Could someone please give me any suggestions, techniques or optimisation tips so that i can load the cities within a short time. I would appreciate it.

Thanks in advance.

short of knowing how your schema is designed Im not sure if anyone can help

I dont think you should see a signigifcant performance problem doing what you want. however a great deal would depend on how your tables(s) were designed, indexed, and how good your queries are.

so as a first step it may help if you detail your table(s) design (identifying the keys and/or realtionships) AND the queries you are using.

do you jnow where the time is being consumed (is it the db, or is it retrieving the information via AJAX (is it a db server, a web server or communications issue)

Hi

Thanks for your reply, I managed to resolve the speed issue by creating separate tables for all the city data and it now loads quite quickly.

Thanks again