COMPOSITE INDEX

COMPOSITE INDEX is a SQL statement in the Indexes category. An index on multiple columns. Column order matters — queries use leftmost prefix. The syntax is CREATE INDEX index_name ON table_name (col1, col2, col3);. It returns composite index. A typical example: CREATE INDEX idx_name_dept ON employees (last_name, first_name, dept_id); -- Useful for: WHERE last_name=? AND first_name=? -- Also useful for: WHERE last_name=? -- NOT useful for: WHERE first_name=? A close relative is CREATE INDEX, which creates an index on one or more columns to speed up query performance. A close relative is DROP INDEX, which removes an existing index from a table. A close relative is EXPLAIN, which shows how MySQL executes a query — which indexes are used, table scan vs index scan. More about this category: Database indexes — CREATE INDEX, UNIQUE INDEX, COMPOSITE INDEX, DROP INDEX. Related Indexes entries: CREATE INDEX, DROP INDEX, EXPLAIN, FULLTEXT INDEX, SPATIAL INDEX, INCLUDE INDEX (Covering Index), PARTIAL INDEX, INVISIBLE INDEX.