MySQL : careful with index_merge

MySQL : careful with index_merge. :: MySQL 4.x was not able to use more than 1 index. MySQL 5.x fixed this with a feature called "index merge".

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 🙂

http://www.mysqlperformanceblog.com/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/

Embedded Link

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.

80 thoughts on “MySQL : careful with index_merge

  1. • Gan, Makasih banget Informasi dari agan memang lagi saya cari, dan Alhamdulillah nyanthol disini. Mantap….Nyus Banget Gan. Langsung mau Ane Praktekin. Buat Pembaca Yang Baru berkunjung….Sering-sering aja Mampir kesini…Infonya selalu Mak Nyus…TOP BGT.

Leave a Reply

Your email address will not be published.