Why mysql use only one index for table? Index merge is used very seldom and often is not efficient.
But search engines like Elastic, Sphinx can use few indexes efficiently.
Either &quot;index merge&quot; is inefficient that most other techniques because of what it has to do:

Gather PRIMARY KEYs based on one index.
Gather PRIMARY KEYs based on the other index.
Sort each list
Merge the lists
Look up the desired rows based on the PKs
Then move on to whatever else is going on.

Furthermore, &quot;Index merge intersect&quot; is almost always less efficient than a composite index that combines the columns being searched on.  See my discussion here:  http://mysql.rjweb.org/doc.php/index1