Polymorphic Indexes in MySQL: Type-First or ID-First?

When building polymorphic associations in Ruby on Rails, the database schema usually looks straightforward: a relation_id (Integer) and a relation_type (String). But beneath the surface, there’s a real question: should your composite index start with the ID or the Type?

Rails defaults to [type, id]. In high-performance scenarios, especially when IDs are “mostly unique” (Athlete IDs are much larger than classroom IDs, say), it’s tempting to flip the order.

The Anatomy of a Polymorphic Query

In Rails, a typical lookup for a FormationPositionView looks like this:

SELECT * FROM game_activities 
WHERE relation_id = 1222 
AND relation_type = 'Planning::Private::Models::FormationPositionView';

In MySQL, the engine uses a B-Tree index. To understand which index order is best, we have to look at how the engine “traverses” that tree.

Strategy 1: The Standard [type, id]

This is the Rails default. It treats the type as a namespace.

The Pros

• Broad Utility: Supports both the full lookup (Type + ID) and “Type-only” queries (e.g., finding all activities for all Athletes).
• Data Locality: All records for a specific model are stored physically near each other in the index leaf nodes.
• Left-Prefix Rule: You don’t need a separate index for the relation_type.

The Cons

• String Comparison: Comparing long class names (strings) is technically slower than comparing integers, though MySQL’s prefix compression makes this difference negligible for most.

Strategy 2: The “High-Selectivity” [id, type]

You might choose this if your IDs are diverse (e.g., Athlete IDs are in the millions, while classroom IDs are in the hundreds).

The Pros

• Fast Narrowing: Since the ID is highly selective (unique or nearly unique), the database narrows the search space to a few rows almost instantly.
• Numerical Speed: Jumping through a tree of integers is the “happy path” for CPU registers.

The Cons

• Narrow Utility: This index is useless if you ever need to find all activities for a specific Type without an ID.
• Fragmentation: Because IDs are added chronologically across different types, the data for a single “Type” will be scattered across the index.

Strategy 3: The “Covering Index” (The Real Secret Sauce)

Regardless of the order, the biggest performance win comes from the fact that both columns are in the index. This creates a Covering Index.

When both columns are in the index, MySQL never has to look at the “Table Data” (disk) to confirm the query. It finds the answer entirely within the index RAM.

Which one should you pick?

The Developer’s Decision Matrix

1. The “Almost Unique” Scenario

If your student_id is 9 digits and classroom_id is 4, they rarely overlap. In this case, [id, type] will find the record in slightly fewer CPU cycles. However, you lose the ability to query by Type alone.

2. The Rails Way

Rails often performs “Dependent Destroys” or “Eager Loading” based on the Type. If you use [id, type], these internal Rails operations may trigger Full Table Scans because they lack a type prefix.

Final Verdict

For most applications, stick to the Rails default: [:relation_type, :relation_id].

The speed gained by putting the ID first is measured in microseconds. The utility lost shows up later, as production bottlenecks the first time you need to query without an ID.

If you have massive scale and truly need both, don’t create two composite indexes. Create one composite [type, id] and one single-column index on [id]. That gets you most of the benefit with minimal overhead.