Read performance when indexing one column vs indexing multiple columns

Question

(MySQL, InnoDB)

I have a table that has columns A, B and C. That table will have a lot of rows, let's say a million rows. I have a query that will be executed very often and will search the table by the A column, so I added an index on the A column.

I have another query that will not be executed as often as the first one but still often enough to qualify for a speed increase. That query will search by all the three rows.

My question is, will the second query have any speed increase from the index on column A or would I have to add indexes on all three columns in order to increase read speed? In other words, in a query where I have three WHERE conditions, will MySQL first search by the indexed column? Because if it does, out of those million rows, the first column would filter out maybe 100 or so, and querying those by the other two columns would be somewhat trivial and wouldn't require an index.

• Rick James: Too much hand waving; let's see SHOW CREATE TABLE and the SELECTs.

Answer 1

The index is a guide to the main table. So your first query will use the Index on column A to identify which rows in the table have the data it wants, then does a key lookup to get the rest of the data and do further filtering if necessary.

So your second query will also use Index A and then do a key lookup and then do further filtering as required.

But you can have just a single index on A,B and C and your first query will still use that index (providing that A is the first column indexed).

So you can have your cake and eat it too. I wouldn't create both indexes, I would just create the one on A,B and C. However, If your table is narrow and only has four or five columns then I would only index on A.

Answer 2

Suppose you need these indexes:

INDEX(a)

for WHERE a = 123 or WHERE a > 876; and

INDEX(a, b, c)

for WHERE a=3 AND b=3 AND c > 666.

The second index will handle both of the example I gave for the first index. So, in virtually all situations, you should DROP the INDEX(a).

More on creating good indexes: https://mysql.rjweb.org/doc.php/index_cookbook_mysql

(If you provide specifics for your situation, I will address it here.)

More

Think of INDEX(a,b,c) as a single BTree ordered by the concatenation of a, b, c.

INDEX(a, b, c) achieves everything that INDEX(a) does, but the former is bulkier,

• asn: For INDEX(a, b, c), does the db create B+ tree for column-a and then after it gets to those desired columns applies filtering mechanism on col-b and col-c? ie-No creation of indexing for col-b, col-c? Or is it something different? PS: Oh, that would just make INDEX(a, b, c) and INDEX(a) same.
• Rick James: @ajaysinghnegi - I hope that the info I added answers your questions.

Answer 3

More indexes use more memory (RAM). Also, that might affect insert and delete time because each index must be updated after insert, update or delete.

I would consider adding index on column A only.

• Rick James: If you have INDEX(a,b,c), it is almost always wasteful to add INDEX(a). Indexes are stored in disk (and cached in RAM -- the buffer_pool).

Answer 4

For a table like yours, with 1 million rows (possibly to grow more in the future) I would go for a composite index on the three columns. The first column should be the one that you'll search also separately. So:

ALTER TABLE yourtable ADD INDEX idx_all (`A`, `B`, `C`);

Or:

ALTER TABLE yourtable ADD INDEX idx_all2 (`A`, `C`, `B`); -- if column C is more selective than B.

This will do good for both your query patterns. The first query will only use the first column and the second one will use the three columns.