You sound like a guy who knows his databases, and I freely admit I don't, but here's what I use to keep my life simple: I work in java, and so I have the benef

You sound like a guy who knows his databases, and I freely admit I don't, but here's what I use to keep my life simple: I work in java, and so I have the benefit of a stack trace where ever I want. So in the class that deals with sql queries I keep a hashmap with stacktraces as keys and total execution times as values. Whenever I feel an application sluggish, I just optimize the first 3-5 queries, which usually it's just "explain" and "alter table add key", and using a cache when it's serious. This allows no other thought whatsoever about optimisation. I don't even use indexes by default, on the grounds that they always slow inserts but not always speed things up.