[SOLVED] Do I need an index on N+1 columns, to efficiently do a SELECT on the N columns, and sort by the other?

Do I need an index on N+1 columns, to efficiently do a SELECT on the N columns, and sort by the other?

Suppose I have a table like this in MySQL:

CREATE TABLE mytable (
    column_A [...],
    column_B [...],
    column_C [...],
    column_X INT(11) UNSIGNED DEFAULT 0, -- type not really relevant
    INDEX a_b_c (column_A, column_B, column_C),
    INDEX x (column_X)
);

so basically it has an index on columns A,B,C and an index con column X.

Now suppose I want to do perform a query like this:

SELECT * FROM mytable
WHERE column_A = <something> AND column_B = <something> AND column_C = <SOMETHING>
ORDER BY column_X

Are the indexes above enough to make this as efficient as it can get, or would it benefit from having also an index on all the four columns?

Solution

Assuming that the 3 tests use =, then this would avoid doing a sort:

INDEX(A,B,C,X)

(And toss INDEX(A,B,C) as being redundant.)

MySQL essentially never uses two different indexes in a single SELECT. Anyway the INDEX(x) is useless in this context.

One way to discover some of what I say:

Run EXPLAIN SELECT on what you have.
Add my 4-col index and rerun EXPLAIN.
DROP the 3-col index and rerun EXPLAIN.

"Key_len" indicates how many columns of the index are used for the WHERE.
"Using filesort" indicates that the index was not used for ORDER BY. (The name is misleading -- the sorting may or may not actually use a disk file; it could be done in RAM.)