Is an index needed or not?

Question

MySQL

We have:
id, uid, pid.
100k uid and for each uid 50 pid.
Choose WHERE [email protected] and [email protected];
Which index is better?
1) only uid
2) uid+pid
The reason why I ask small tables should not be stuffed with indexes.

Answer 1

in principle, id is not needed here. uid can be left as the primary key. with such a selection, the index for pid is superfluous.

Answer 2

1. if possible - clustered index by id
2. separate indexes for uid and pid

Answer 3

If reads predominate over writes, then option 2 is preferable.
If records are inserted in batches and occasionally, then you can turn off the index, and then rebuild them.
If the write dominates the read, then perhaps the indexes will only get in the way.
Another option is to partition the hash by uid. Then the speed of neither reading nor writing will be lost.

Answer 4

Option 2