Home > Find Queries That Lack Indexes

Find Queries That Lack Indexes

It’s an ancient tradition amongst MySQL DBAs to analyze slow query log files and sort the results by the so-called “index ratio” to find badly indexed queries. The “index ratio,” in this case, is a heuristic derived from the ratio of rows read to rows returned. The thinking goes that if a query is reading lots of rows and returning only a few, it might be missing an index. The trouble with this is that it’s very prone to false positives: GROUP BY queries, for example, will often read lots of rows and aggregate them into few. Instead of relying on a surrogate measure such as this sometimes-accidentally-right ratio, why not get the truth straight from the horse’s mouth? MySQL sets a flag in a query when it doesn’t use an index. And SolarWinds® Database Performance Monitor sniffs this flag right off the wire and generates metrics from it. To rank queries by their frequency of no-index-used flag, just go to Top Queries and change the Rank By menu to “Missing Indexes.” Voila! Bonus tip: explore the other options in the Rank By menu, too! Lack_Indexes
Baron Schwartz
Baron is a performance and scalability expert who participates in various database, open-source, and distributed systems communities. He has helped build and scale many large,…
Read more

Tweets

SolarWinds's Twitter avatar
SolarWinds
@solarwinds

SolarWinds-Webcast am 16. Dezember: „So gewinnen Sie einen ganzheitlichen Blick auf das Netzwerk“. Netzwerkmanageme… t.co/MZhXqq27So

SolarWinds's Twitter avatar
SolarWinds
@solarwinds

In this webcast, Head Geek @kekline is joined by @MMarie and Jon Moore to discuss how to be proficient in the use o… t.co/9PhqVp1anv

SolarWinds's Twitter avatar
SolarWinds
@solarwinds

Looking for the right subscription service to handle your business’ IT management needs? Look no further than flexi… t.co/YsHaDsaQLl