Historically it was considered that MySQL will generally use only one index per referenced table in a SQL query. In MySQL 5.0 the introduction of merge indexes enabled for certain conditions the possibility to utilize two indexes however this could result in worst performance then creating a better index. In MySQL 5.1 it became possible to control optimization switches with the optimizer_switch system variable.
However in explaining how to utilize the intersection, union and sort union in queries I discovered that MySQL could use three indexes for one given table.
Extra: Using union(name,intersect(founded,type)); Using where
I was not aware of this.
In MySQL this optimization is known as index merge optimization, and it works with both AND operations and OR operations., and that actually means that multiple indexes can be used per single table,. You have mentioned that this optimization can have worst performance,. is there any specific use case you are referring to here?