Summary
Add a rule to detect implicit type conversions in WHERE clauses that prevent index usage.
Why This Matters
Implicit type conversion is an invisible performance killer:
- Index bypass — database can't use index when types don't match
- Silent problem — no error, query just runs slowly
- Hard to diagnose — EXPLAIN shows index scan, but not why
- Common mistake — especially with string/numeric mismatches
How It Happens
-- Column: phone_number VARCHAR(20), indexed
-- Query:
SELECT * FROM users WHERE phone_number = 1234567890;
-- Database internally converts to:
SELECT * FROM users WHERE CAST(phone_number AS INTEGER) = 1234567890;
-- Index on phone_number is now USELESS because function applied to column
Real Performance Impact
-- Indexed VARCHAR column compared with number
SELECT * FROM orders WHERE order_code = 12345;
-- Without index: 50ms on 1M rows
-- Expected with index: 1ms
-- Actual (implicit cast): 50ms — index ignored!
Rule Specification
| Property |
Value |
| Rule ID |
PERF015 |
| Name |
Implicit type conversion |
| Category |
Performance |
| Severity |
Warning |
| Default |
Enabled |
Detection Patterns
-- Should trigger (common implicit conversion patterns)
WHERE varchar_column = 123 -- string compared to number
WHERE date_column = '2024-01-01 00:00:00' -- date vs timestamp string
WHERE char_column = 'a' -- CHAR vs VARCHAR potential issue
-- Schema-aware detection (if schema provided)
WHERE phone VARCHAR = 123 -- literal number vs string column
WHERE created_at TIMESTAMP = '2024-01-01' -- date string vs timestamp
Suggested Message
"Possible implicit type conversion: comparing string column with numeric literal (or vice versa). This prevents index usage. Use matching types: WHERE phone_number = '1234567890'"
References
Implementation Notes
- Detect: unquoted numbers compared with known string patterns (
phone, code, sku, email)
- Detect: quoted dates compared with known timestamp columns
- Schema-aware mode: use actual column types for precise detection
- Consider: numeric literal without quotes next to
=, <, >, IN
- This rule benefits greatly from schema information
Summary
Add a rule to detect implicit type conversions in WHERE clauses that prevent index usage.
Why This Matters
Implicit type conversion is an invisible performance killer:
How It Happens
Real Performance Impact
Rule Specification
PERF015Detection Patterns
Suggested Message
References
Implementation Notes
phone,code,sku,email)=,<,>,IN