However, there are times when this feature actually slows down the performance.
An excellent example & explanation about this is discussed on the article I linked here.
I experienced a similar situation – a query is reading multiple indexes + intersect + sort it = CPU intensive, AND turned out it's executed several hundred times per #second.
It ended up overloading the database server pretty spectacularly 🙂
The (temporary) quick fix is to execute the following command in MySQL console :
SET GLOBAL optimizer_switch="index_merge_intersection=off"
Then once you implemented the correct solution to the problem (change the query / create a new compound index / etc), don't forget to turn it on again.
Otherwise other "innocent" queries might suffer because of it 🙂
The Optimization That (Often) Isn’t: Index Merge Intersection – MySQL Performance Blog
Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that didnâ€™t understand this limitation would often have tables with lots of single-column indexes on columns which commonly appeared in their WHERE clauses, and theyâ€™d wonder why the EXPLAIN plan for a given SELECT would [â€¦]
Post imported by Google+Blog for WordPress.