in my mysql database i've got the geonames database, containing all countries, states and cities.

i am using this to create a cascading menu so the user could select where he is from: country -> state -> county -> city.

but the main problem is that the query will search through all the 7 millions rows in that table each time i want to get the list of children rows, and that is taking a while 10-15 seconds.

i wonder how i could speed this up: caching? table views? reorganizing table structure somehow?

and most important, how do i do these things? are there good tutorials you could link to me?

i appreciate all help and feedback discussing smart ways of handling this issue!