read/write table efficiency

Hey mysql gurus,

I've got a bit of an efficiency question, and just trying to figure out how best to manage this.

For my site HearWhere.com, I have a table which holds artists shows.
When i am refreshing the collection of shows and geocoding the locations, the database response for user queries slows significantly - which is understandable.

One option I'm considering is to have one database which the users connect to for queries, and a second separate database for writing the updates, and then I can just move the updated database to become the live database on a daily basis.

The other option I think is just to have a separate table which I write the updates to, and then just replace the table the users interact with from the table which was updated.

Either way I go, at the moment everything would be done on the same sql server - I don't currently have enough traffic to justify multiple servers.

I hope I've described this well. Is there a better way to be doing this? Is one method better than the other?

re: read/write table efficiency

Well, I believe, the way with separate table will suffice.
Alternatively, You can set user selects (if only selects are performed to serve users) to have more priority than updates and inserts that refresh data. http://dev.mysql.com/doc/refman/5.1/...e-locking.html
There no magic is to be expected for You have a shared resource at the end - table file. So, either access it with locks ( concurrent users' selects and scheduled updates) or have separate files and don't clash queries. Take into account also that if You have indexes in the table to be updated it is more efficient to lock it once before all update queries and unlock after than to just perform separate updates. This is because in the former case indexes are updated only once - upon unlocking and in the latter - after each update statement involving index columns.

re: read/write table efficiency

Awesome Denis,
I haven't actually solved this yet, but it seems the seperate table is the simplest and hopefully most efficient. I'll give that a go.

I'm going to start using memcache for caching queries, so that should go a long way to alleviating issues as well.

Thanks for the help,
Pete