Find Queries That Need Indexes With VividCortex

Posted by Baron Schwartz on Sep 21, 2017 4:31:49 PM

Queries can be slow or resource-heavy for many reasons, but one of the most common is that the table doesn't have a good index for the query. This is true for MySQL, PostgreSQL, and especially for MongoDB. Adding an index where it's missing is often a night-and-day improvement, as shown in this screenshot of a query's performance. The index was added midway through the time range, and the query got dramatically faster:

GitHub Query Performance

(That image is from Rocio Delgado's Query Performance At Scale presentation about process and tooling at GitHub).

Discovering queries that can benefit from adding indexes is typically a task for an expert, but at VividCortex we've made it possible for anyone, even a novice, to figure out which queries to examine. Our smart algorithms prioritize which queries can benefit most from speedups, and then look at which of those need indexes. We show these queries in a new widget on the VividCortex default dashboard:

Queries That Need Indexes.png

Boom! Even if you're not familiar with databases, execution plans, and indexes, you can get the answers you need. No more ranking, slicing, dicing, and thinking about what it all means. Just immediate guidance: look at these queries, because they're likely to need indexes. Click on any of them and you'll go right into the detail view where you can examine individual executions of queries, look at EXPLAIN plans, and so on.

The rank at the left edge of the widget shows these queries' ranks relative to all queries. This means that this app's 5th, 7th, and 10th most time-consuming queries all aren't using indexes. That's a huge opportunity!

Behind the scenes, this works by sifting through multi-dimensional performance data belonging to the hosts during the interval you've selected. In the example above, we've found the most abusive unindexed queries over 195 database hosts.

You can imagine how much of a time-saver this is: it would take you weeks to do the old-fashioned way. Not only does this feature save you lots of time, but potentially huge amounts of resources, too. It makes it a lot easier to delight your customers with a faster experience.

Stay tuned: we're enhancing VividCortex to give you many more immediate answers to your most pressing questions, even if you didn't know you needed them!

Finally, we're always glad to hear your suggestions. What can VividCortex do for you? Just use the in-app chat to ask us for help or suggest a feature.

Recent Posts

Posts by Topic

see all