Search code examples
mysqlsqlexplain

creating a multi column index with the primary key

My table:

CREATE TABLE test (
  id INT AUTO_INCREMENT,
  anotherID INT,
  PRIMARY KEY (id),
  INDEX testIndex (id, anotherID)
);
INSERT INTO test VALUES (1,1),(2,1),(3,1),(4,1);

My query:

EXPLAIN SELECT *
FROM test
WHERE id = 1
  AND anotherID = 1;

Per that only the PRIMARY key is being used. testIndex, however, is not. My question is... why? Since the SELECT is referencing values in both id and anotherID shouldn't testIndex be used?

The SQL fiddle:

http://sqlfiddle.com/#!2/b833d9/1

Solution

  • The primary key is unique, so the additional check isn't even necessary. It doesn't get more unique than the primary key. So there is no additional benefit in this case to use the other index.