sql - 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?

Answer 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.