Multiple and single indexes

Question

I'm kinda ashamed of asking this since I've been working with MySQL for years, but oh well.

I have a table with two fields, a and b. I will be running the following queries on it:

• SELECT * FROM ... WHERE A = 1;
• SELECT * FROM ... WHERE B = 1;
• SELECT * FROM ... WHERE A = 1 AND B = 1;

From the performance point of view, is at least one of the following configurations of indexes slower for at least one query? If yes, please elaborate.

1. ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);
2. ALTER TABLE ... ADD INDEX (a, b);
3. ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);

Thanks (note that we are talking about non unique indexes)

Solution 1

Yes, at least one case is considerably slower. If you only define the following index:

ALTER TABLE ... ADD INDEX (a, b);

... then the query SELECT * FROM ... WHERE B = 1; will not use that index.

When you create an index with a composite key, the order of the columns of the key is important. It is recommended to try to order the columns in the key to enhance selectivity, with the most selective columns to the left-most of the key. If you don't do this, and put a non-selective column as the first part of the key, you risk not using the index at all. (Source: Tips on Optimizing SQL Server Composite Index)

Solution 2

It's very improbable that mere existence of an index slow down a SELECT query: it just won't be used.

In theory the optimizer can incorrectly choose more long index on (a, b) rather than one on (a) to serve the query which searches only for a.

In practice, I've never seen it: MySQL usually does the opposite mistake, taking a shorter index when a longer one exists.

Update:

In your case, either of the following configurations will suffice for all queries:

(a, b); (b)

or

(b, a); (a)

MySQL can also use two separate indexes with index_intersect, so creating these indexes

(a); (b)

will also speed up the query with a = 1 AND b = 1, though to a lesser extent than any of the solutions above.

You may also want to read this article in my blog:

• Creating indexes

Update 2:

Seems I finally understood your question :)

ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);

Excellent for a = 1 and b = 1, reasonably good for a = 1 AND b = 1

ALTER TABLE ... ADD INDEX (a, b);

Excellent for a = 1 AND b = 1, almost excellent for a = 1, poor for b = 1

ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);

Excellent for all three queries.

Solution 3

SQL will choose the index that best covers the query. An index on A, B will cover the query for both case 1 and 3, but not for 2 (since the primary index column is A)

So to cover all three queries you need two indexes:

ALTER TABLE ... ADD INDEX (a, b); ALTER TABLE ... ADD INDEX (b)