I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about 500k rows in each table.
We have optimized the query cache as good as possible (to my knowledge from the extensive research I did) but whenever one of the tables is changed and the data in the cache has to be removed the server starts to lock up and the mysql tmp dir fills up and the server load goes sky high and the following has to be done to fix the issue:
- Stop mysql (load goes to normal)
- Repair the database tables
- Clear cache from mysql
- Flush memcache
- Repair database tables again
- Restart mysql
I didn't code the site nor did I design the database schema, but I have noticed in the code that the
SELECT's that make use of the
UNION's all start with a
parentheses and hence shouldn't be cached; though if they aren't cached, then it doesn't explain why the site goes haywire whenever a record is deleted and the cache would be cleared for those tables.
I'm told that MySQL should be able to handle 39k visits/day fine; so I'm wondering if the database design could be improved and if so how?
Obviously putting all the data into one big table is an even worse design than it has now, but I'm unsure what other options there are if it isn't one big table or category based data.
Below is a screenshot of an
EXPLAIN which seems to show that the queries are indexed fine:
http://i.stack.imgur.com/jyjlJ.png (Linked it as site was resizing it and was unreadable)
I've searched around the net but couldn't find much of anything that documents other category based data models.