I have a big table containing some location data. In my application user can find for example some monument in England or add some monument to any other location.
I want to speed up the the search process.
I can have separate tables for each country but then i need some mechanism to dynamically create one when user is about to add some place which is in the country that doesn't have corresponding table.
I would appreciate some remarks on how to tackle this problem.
What are you using for your database? Are you caching anything?
There are several ways to tackle this: Use Redis, Implement Ajax (query for less results at one time.), partition your database, implement better indexing, caching, reducing the number of queries you make to the database, etc.