shri - 9:36 am on Mar 16, 2013 (gmt 0)
The best way to optimize your database (and your application stack) is to ensure that you have rock solid caching at multiple levels.
Rendered pages do not need to be rendered again, unless something has changed - content edited, comments added etc. Use something like Varnish with content sensitive purging ( available in W4TC plugin).
This is far superior to application level caching as neither PHP nor MySQL are accessed unless your page has changed.
Also consider using a plugin like W3TC to cache queries and a bunch of other stuff in a memcached like setup. This will ensure further scalability.
Using these strategies we were able to get the query load down from 1000+ queries per second down to 20-50 queries per second on a WP based site that had around 2 million visitors over a weekend.
With a low query load like that you don't have to mess with changing your DB structure / indices etc.