speed up a sql query to mysql?
- by fayer
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!