SQL Multiple‐Column Indexes - Yash-777/MyWorld GitHub Wiki

MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 10.3.5, “Column Indexes”).

The order of columns in a MySQL multi-column index is crucial for query performance due to the "leftmost prefix" principle. This principle dictates that MySQL can utilize a composite index for queries that filter or sort on the leading (leftmost) column(s) of the index. Key considerations for ordering columns in a multi-column index:

Leftmost Prefix Matching:

MySQL can use the index if the query's WHERE clause or ORDER BY clause starts with the first column(s) defined in the index. For example, an index on (col1, col2, col3) can be used for queries filtering on (col1), (col1, col2), or (col1, col2, col3).

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

It cannot directly use the index for queries filtering only on col2 or col2, col3 independently.

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;