O'Reilly logo

SQL Antipatterns by Bill Karwin

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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

Measure

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

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required