Indexes. Which is better to create?

Question

MySQL

Imagine the following table structure:

Field name | An approximate ratio of unique data in each field to the total number in the table, for example, 3 million rows:

link_id | 1-5
field1_id | 100
field2_id | 2500
field3_id | 3500
field4_id | 50

i.e. out of 3 million records, for example, if you take:
- link_id , then there will be from 1 to 5 unique ones;
- field1_id - there will be about 100 unique ones, etc...

As you probably understood, this is a "connecting" table... The
selection can be made "cross" on any fields... but mostly such situations (if we simplify directly very):

1. Select all unique field1_id with match conditionfield4_id ;
2. Select all unique field2_id with match condition field1_id AND field4_id ;
3. Select all unique field3_id with match condition field2_id AND field4_id ;
4. Select all unique link_ids with match condition field2_id AND field3_id AND field4_id .

UPD:
Above in the conditions for each field more often WHERE IN ... and between them AND ...

How would you place the indices?

I think from composite ones , at least, to make the following:
field1_id_field4_id
field2_id_field3_id_field4_id

From the single ones:
link_id
field4_id

I don't want to produce "extra" ones... you can also use a separate index for each field, of course... but this is not always correct, and when adding / updating the speed will be lost... but it is also expected to be frequent changing data...

Answer 1

In short, if someone needs a similar "layout" in a similar data structure in a table ...
It turned out like this:
Single:
INDEX field4_id
INDEX link_id
Composite:
INDEX field4_id, field2_id, field3_id
INDEX field4_id , field1_id, field2_id, field3_id either there are extra indexes, or something is missing... :) For a long time there was no "experiments" with MySQL... I just wanted to consult with those who had already eaten the "dog" on this, so to speak)) For me the result turned out to be acceptable... The data ratio is given in the question...so I consider this an answer. I would be happy to accept the amendments, of course!

Thank you)

Answer 2

In such non-obvious cases, it is necessary to model. On more or less real volumes.