Menu

"Same Leading Column on Two Indexes? Ain't One of 'Em a Waste?" — How to Find and Drop Redundant Indexes
📰

"Same Leading Column on Two Indexes? Ain't One of 'Em a Waste?" — How to Find and Drop Redundant Indexes

DEV Community: rails·Kaziu·about 6 hours ago
#TS4fWinf
#devtorails#devto#dev#index#import_id
Reading 0:00
15s threshold

Have you ever been looking through a table's indexes and spotted a pair like this sitting side by side?

KEY `idx_logs_on_import_id` ( `import_id` ) UNIQUE KEY `idx_logs_on_import_id_and_file` ( `import_id` , `file_path` )

Enter fullscreen mode

Exit fullscreen mode

Spoiler: you can usually drop the single-column one on top . In this post I'll walk through "why you can drop it," "whether you really should (and the exception where you can't)," "how to find these mechanically," and "how to remove them safely."

Why the single (import_id) index is redundant

The key is a property of B-Tree indexes: the "leftmost prefix" of a composite index also works as a single-column index .

An index on (import_id, file_path) is sorted first by import_id , and then by file_path within that.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.