One of our top queries updates a table called host_agent with status information from agents checking in with the APIs periodically. I was always a little surprised at how "hot" this particular query is. If you'd asked my intuition, I wouldn't have thought it would be in the top 25.

Over time, a thought occurred to me -- this table is a bit denormalized and needs to be refactored, and as we made progress towards that in small steps, its primary key was revealed to be wrong. That is, it wasn't truly the natural primary key for the table. And the hot update had to use a secondary index to search for the row it wanted to update. Hmmm, I thought, didn't I write High Performance MySQL and shouldn't I know a little bit about these things?

I could have tested things endlessly with measurements or benchmarks or something, but I love using VividCortex on our own systems. Pour me a glass of that VividChampagne, I'll drink it! So I just ran an ALTER TABLE on our staging system, waited a bit, and looked at our Top Queries view. To get the view I wanted, I just sorted by average latency (instead of the default, which is total time) and filtered the results by the table name. Behold:

See the spike in the first query? That's the ALTER. The query I thought would get a lot faster after this index change was #2. It doesn't look like it changed at all.

On the other hand, take a look at queries 4, 6, 7, and 9. They all suffered badly from the change in index.

I don't need to get deep into exactly what the EXPLAIN plan is and why this query is suddenly slower than it used to be. The key thing is knowing how easily I can visualize what happened to performance of all of the queries affecting this table. Without VividCortex I can safely say I would have been quite unaware of the changes in most of those queries, which I didn't expect to suffer.

In the future, such an ALTER TABLE will automatically generate an event in our system, which will be overlaid on views such as this for even easier inspection. (We have routine ALTER events, but this one isn't one of them; it's unusual and will be categorized as such.)

Note that this entire experiment took much less time than I've just spent writing about it. No slow query logging, not tedious comparing before and after. Do your tools help you get your work done this quickly?