Summary
Add a schema-aware rule to detect JOINs on columns that lack indexes, particularly foreign key columns.
Why This Matters
JOINs on unindexed columns cause severe performance issues:
- Nested loop with full scan — without index, database scans entire table for each row
- O(n × m) complexity — 10K × 10K = 100M row comparisons
- Memory pressure — may spill to disk for large joins
- Common oversight — foreign keys don't auto-create indexes in all databases
The Misconception
Many developers assume:
"I created a foreign key, so it must have an index"
Reality by database:
| Database |
FK Auto-Creates Index? |
| MySQL (InnoDB) |
Yes (only InnoDB) |
| PostgreSQL |
No |
| SQL Server |
No |
| Oracle |
No |
| SQLite |
No |
Performance Impact Example
-- Table: orders (1M rows), FK: user_id references users(id)
-- NO INDEX on orders.user_id
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.name = 'John';
-- With index on user_id: 5ms
-- Without index: 15 seconds (3000x slower!)
Rule Specification
| Property |
Value |
| Rule ID |
SCHEMA004 |
| Name |
JOIN on non-indexed column |
| Category |
Performance |
| Severity |
Warning |
| Default |
Enabled (requires schema) |
Detection Logic
- Parse JOIN conditions (ON clause)
- Extract columns being joined
- Check schema: does column have an index?
- If not indexed → flag as warning
Detection Patterns
-- Should trigger (if schema shows no index on orders.user_id)
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
SELECT * FROM products p LEFT JOIN reviews r ON p.id = r.product_id;
-- Should NOT trigger (indexed columns)
SELECT * FROM users u JOIN orders o ON u.id = o.user_id; -- if user_id indexed
Suggested Message
"JOIN on column 'orders.user_id' which has no index. JOINs on unindexed columns cause full table scans. Add index: CREATE INDEX idx_orders_user_id ON orders(user_id);"
Schema File Support
This rule requires schema information:
{
"tables": {
"orders": {
"columns": ["id", "user_id", "amount"],
"indexes": [
{"name": "pk_orders", "columns": ["id"]}
// Note: user_id is FK but not indexed!
]
}
}
}
References
Implementation Notes
- Requires
--schema flag to be useful
- Parse ON clause to extract join columns
- Cross-reference with schema indexes
- Match column names (handle aliases)
- Consider: composite indexes covering join column
- Output: include CREATE INDEX suggestion with proper column name
Summary
Add a schema-aware rule to detect JOINs on columns that lack indexes, particularly foreign key columns.
Why This Matters
JOINs on unindexed columns cause severe performance issues:
The Misconception
Many developers assume:
Reality by database:
Performance Impact Example
Rule Specification
SCHEMA004Detection Logic
Detection Patterns
Suggested Message
Schema File Support
This rule requires schema information:
{ "tables": { "orders": { "columns": ["id", "user_id", "amount"], "indexes": [ {"name": "pk_orders", "columns": ["id"]} // Note: user_id is FK but not indexed! ] } } }References
Implementation Notes
--schemaflag to be useful