It's a bit of a black art, tweaking the performance and tuning of multi-user database systems. Once upon a time, that was my job, and I'd be lying if I said it was always fun. It was rather depressing when something which should have worked to improve things didn't. And then there were times when you found a minor change which broke open a huge bottleneck, but then people would argue with you that certainly THAT couldn't have been the reason. (Usually, programmers. :) I was actually pretty good at it, but I was working on large systems where we had pretty decent diagnostic tools. Really expensive stuff, but in an environment where an outrageously overpriced $25K bit of software to monitor database calls could earn its purchase price the afternoon we installed it and spotted the first problem.

Depending on your systems architecture, there are all sorts of tricks you can use to speed things up. With commercial relational databases like the ones I worked on, quite often creating the right index would make all the difference in the world. I'm not familiar with MySQL, but if that's what the CMS is using to store its rules and page contents, I'd look there first. Other times, hardware (specifically, how a database is distributed on disk) is the solution. Splitting a large database up between different physical disks, so that frequently-executed complicated joins of multiple tables can access the tables simultaneously was one way to eke out more performance, usually far cheaper than trying to speed up the processor and memory. Of course, you need intimate knowledge of how the application is using the data, and which operations you should "cheat" and favor, something that might not be possible in a black box Content Management System like Drupal. Even harder if the tools don't even exist.

I wish I knew something about MySQL and Drupal. I suspect the answer is in there, somewhere. I do know that the processors in today's x86 systems are way faster than the heavy-duty minicomputers that major companies were using not all that long ago to run their businesses. Not only that, but the amount of main system memory has grown enormously in recent years. You've got to be bottlenecking at the disk access level. That's the physically slowest part of a computer, and a non-optimized or missing index makes things a million times worse. I wish you the best of luck in finding a fast, cheap, and easy way to improve performance. Indexing, and tinkering with the physical distributions of the tables of a database on disks -- those are nifty tricks if you have direct control over those sorts of things, and even better if you can figure out where to use them. It's not always possible in a proprietary system.