Two indexes with one column versus one two-column index in MySQL?

Question

I came across the following and I am not sure what is the best practice.

Consider the following table (which will become large):

id PK | giver_id FK | recipient_id FK | date of

I use InnoDB and, as I understand it, it automatically creates indexes for two foreign key columns. However, I will also perform many queries where I need to match a specific combination:

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

Each such combination will be unique in the table.

Is there any use in adding a two-column index on these columns or two theoretical theories? //

Answer 1

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

If you have an index with two columns, the query may be faster (you have to measure). The index of two columns can also be used as the index of a column, but only for the first column.

It is sometimes useful to have an index on (A, B) and another index on (B). This makes queries using one or both columns fast, but, of course, uses more disk space.

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

Answer 2

Coverage Index as:

 ALTER TABLE your_table ADD INDEX (giver_id, recipient_id); 

... means that the index can be used if the request refers to giver_id or a combination of giver_id and recipient_id . Keep in mind that the index criteria are left-based - a query related only to recipient_id will not be able to use the coverage index in the application I submitted.

In addition, MySQL can only use one index for SELECT, so the coverage index will be the best way to optimize your queries.

Answer 3

If one of the foreign key indexes is already very selective, then the database engine should use this for the query you specify. Most database engines use some kind of heuristic to be able to choose the optimal index in this situation. If neither index is very selective in itself, it probably makes sense to add an index based on both keys, as you say you will use this type of query.

Another thing to keep in mind is to exclude the PK field in this table and determine the primary key index in the giver_id and recipient_id fields. You said that the combination is unique, so perhaps it will work (given the many other conditions that you can only answer). Generally, although, I think, the added complexity that is added is not worth the hassle.

Answer 4

Another thing is that the performance characteristics of both approaches will be based on the size and power of the data set. You may find that an index with two columns becomes more visible with more certainty at a certain threshold for the size of the data set or the exact opposite. Nothing can replace performance metrics for your exact scenario.