There’s a tendency to think all problems can be solved with an index or two (or partitions, or other performance trick of your choice), but sometimes the answer is to not do the work to start with. Sounds Zen, but it’s often a matter of caching in the write place. It’s a technique I’ve used a few times in interesting ways, but maybe never quite to the scale mentioned in this article about about Accuweather. By doing something pretty interesting yet pretty simple out in front of the database they reduced their workload from 300 to 500…million requests per day.
It’s good to tune, good to have the right indexes, but tuning requires understanding the entire problem.