Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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:

  1. Stop mysql (load goes to normal)
  2. Repair the database tables
  3. Clear cache from mysql
  4. Flush memcache
  5. Repair database tables again
  6. 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.

share|improve this question
What do you mean by "whenever one of the tables is changed"? A column added to it, or just rows added/updated/deleted? – Phil Aug 28 '12 at 21:26
3  
Oh, and by the way, putting all of the data into one table with an extra category column may actually be better than the UNIONs you're currently doing – Phil Aug 28 '12 at 21:28
Are all these 45 tables identical in structure? Are all your queries using fulltext indexes? Would you consider moving the tables to InnoDB (from what you have, I assume MyISAM) so you don't have to repair tables? – ypercube Aug 28 '12 at 22:50
@phil I mean whenever an update/delete/insert happens. As for your other comment about doing it on one table, I guess this is a preference thing, as I have had other people comment elsewhere that doing it in one table would be far worse. – Brett Aug 29 '12 at 7:37
@ypercube Yes, they are identical in structure and yes the majority of the queries make use of the fulltext indexes. Can't move to InnoDB as that doesn't suppirt fulltext indexes, but have considered using something such as Sphinx on top of it if need be. – Brett Aug 29 '12 at 7:39
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.