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.

Thanks in advance.

you need to optimise the queries, but without knowing the data structures it's impossible to be precise.

One quick way would be to redesign any queries using "SELECT * FROM ..." to use a fieldlist instead.

I doubt that using AJAX is the most efficient way either as it is obviously very dependent on the client browser for the operational speed.
Some things are best done with a round trip to the server.

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 for your suggestion.