Two single-column indexes vs one two-column index in MySQL?

Question

I'm faced with the following and I'm not sure what's best practice.

Consider the following table (which will get large):

id PK | giver_id FK | recipient_id FK | date

I'm using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, I'll also be doing lots of queries where I need to match a particular combination of:

SELECT...WHERE giver_id = x AND recipient_id = t.

Each such combination will be unique in the table.

Is there any benefit from adding an two-column index over these columns, or would the two individual indexes in theory be sufficient / the same?

Solution 1

If you have two single column indexes, only one of them will be used in your example.

If you have an index with two columns, the query might be faster (you should measure). A two column index can also be used as a single column index, but only for the column listed first.

Sometimes it can be useful to have an index on (A,B) and another index on (B). This makes queries using either or both of the columns fast, but of course uses also more disk space.

When choosing the indexes, you also need to consider the effect on inserting, deleting and updating. More indexes = slower updates.

Solution 2

A covering index like:

ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

...would mean that the index could be used if a query referred to giver_id, or a combination of giver_id and recipient_id. Mind that index criteria is leftmost based - a query referring to only recipient_id would not be able to use the covering index in the statement I provided.

Please note that some older MySQL versions can only use one index per SELECT so a covering index would be the best means of optimizing your queries.

Solution 3

If one of the foreign key indexes is already very selective, then the database engine should use that one for the query you specified. Most database engines use some kind of heuristic to be able to choose the optimal index in that situation. If neither index is highly selective by itself, it probably does make sense to add the index built on both keys since you say you will use that type of query a lot.

Another thing to consider is if you can eliminate the PK field in this table and define the primary key index on the giver_id and recipient_id fields. You said that the combination is unique, so that would possibly work (given a lot of other conditions that only you can answer). Typically, though, I think the added complexity that adds is not worth the hassle.

Solution 4

Another thing to consider is that the performance characteristics of both approaches will be based on the size and cardinality of the dataset. You may find that the 2-column index only becomes noticing more performant at a certain dataset size threshold, or the exact opposite. Nothing can substitute for performance metrics for your exact scenario.

View more solutions

Comments

[sguven]

If the combination of the two columns is unique, you could create a two-column index with unique feature which will not only increase the speed of your query but also add consistency to your table.

[AlikElzin-kilaka]

"MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table." - Multiple-Column Indexes

[Erk]

To extrapolate on @user1585784; If the combination of the two columns is unique, I think one should use a unique key for them. In fact, if one wants to enforce the uniqueness on the database level, a unique key is the easiest way to go...

[Tom]

Thanks Mark, one of the keys is indeed very selective so it should be fine. I've opted to keep the two (automatic) indices in place and see how it performs over time. I also thought about a combined giver:recipient primary key, but as each field also needs to be searchable individually, it would just add php overhead. Also, the new key would be a (longer) string instead of a (shorter) integer.

[Davor]

MySQL can only use one index per SELECT this isn't true anymore, it would be nice if you edited your answer to be updated.

[Ivo Pereira]

Would you mind to explain why the covering index would not be able to be used by recipient_id?

[Slicktrick]

@IvoPereira Multi column indexes in MySQL let you use all the fields in the index from left to right. For example if you have an INDEX (col1, col2, col3, col4) then the index will be applied for searches with a WHERE clause like col1 = 'A' or col1 = 'A' AND col2 = 'B' or col1 = 'A' AND col2 ='B' AND col3 = 'C' AND col4 = 'D', but this particular index won't be used for anything like WHERE col2 = 'B' or WHERE col3 = 'C' AND col4 = 'D' because the search fields are not left most in the index definition. You would have to add additional indexes to cover those fields.

[AlikElzin-kilaka]

"MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table." - Multiple-Column Indexes

[oldboy]

"one index per SELECT", is this still true for mariadb 10.1?

[kapad]

@Anthony: No. see the comment by Davor above.

[kapad]

could you please link to some documentation around this. Thanks.

[Izaya]

In some cases, MySQL will use several single indexes for one query instead of only one single index. This is called index merge optimization. This isn't always a good optimization cf

[stevec]

A two column index can also be used as a single column index, but only for the column listed first - is that also true for postgres databases?