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. In the VividCortex application, we show these queries in the Profiler:

Profiler Missing Indexes Screen

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 column at the left edge of the widget shows how these queries stack up against all queries with missing indexes and ranks the top five queries by volume of missing indexes. In the column immediately to the right of the widget, we can see the total execution time for each query in the past day. This makes it easy to identify queries that are missing indexes and quickly understand the total time each query spent executing.

In the example above, we monitored performance across 87 database hosts and quickly uncovered the most abusive unindexed queries that ran over the past day. That's a huge opportunity for improvement!

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