Find Queries That Need Indexes With SolarWinds Database Performance Monitor
September 21, 2017 |
Database
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. The index was added midway through the time range, and the query got dramatically faster.
Discovering queries that can benefit from adding indexes is typically a task for an expert, but 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 SolarWinds® Database Performance Monitor (DPM), we show these queries in the Profiler:
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 SolarWinds DPM to give you many more immediate answers to your most pressing questions, even if you didn't know you needed them!