Solution: MENTOR Your Indexes

The Index Shotgun antipattern is about creating or dropping indexes without reason, so let’s come up with ways to analyze a database and find good reasons to include indexes or omit them.

We can use the mnemonic “MENTOR” to describe a checklist for analyzing our database for good index choices: Measure, Explain, Nominate, Test, Optimize, and Rebuild.

Measure

We can’t make informed decisions without information. Most databases provide some way to log the time it takes to execute SQL queries so we can identify the operations with the greatest cost. For example:

• Microsoft SQL Server and Oracle both have SQL Trace facilities as well as tools to report and analyze trace results. Microsoft calls this tool the SQL Server Profiler, and Oracle calls it TKProf.

• MySQL and PostgreSQL can log queries that take longer to execute than a specified threshold of time. MySQL calls this the slow query log, and its long_query_time configuration parameter defaults to $10$ seconds. PostgreSQL has a similar configuration variable log_min_duration_statement. PostgreSQL also has a companion tool called pgFouine, which helps us analyze the query log and identify queries that need attention.

Once we know which queries account for the most time in our application, we know where we should focus our optimizing attention for the greatest benefit. We might even find that all queries are working efficiently except for a single bottleneck query. This is the query we should start optimizing.