EXPLAIN Execution Plans
EXPLAIN is the foundational tool for MySQL query optimization. Focus on the type column (from best to worst: system > const > eq_ref > ref > range > index > ALL), rows column (scan count), and Extra column (index usage, temporary tables, filesort).
Index Design Principles
Leftmost prefix rule: composite index (a,b,c) supports queries on a, a+b, and a+b+c. Covering indexes: all queried columns exist in the index, avoiding table lookups. Index Condition Pushdown (ICP): MySQL 8.0 pushes WHERE conditions to the storage engine, reducing lookups.
Common Pitfalls
Using functions on indexed columns invalidates indexes; implicit type conversions can invalidate indexes (e.g., comparing VARCHAR with numbers); OR conditions may not use indexes (consider UNION ALL instead).
Small team, big output. iDev builds web apps, AI solutions and custom systems with startup speed and enterprise quality. Based in Malaysia, serving Southeast Asia. Free consultation.
