Problem with index_merge

[atitthaker]

Hello all,

I have struck with a big problem with MySQL 5.0.22 server on RHEL 3.

After an upgradation from MySQL 4.1.11 to MySQL 5.0.22 almost all the queries are struggling to execute and the DB server is clogged. Below is an example of what is happening.

This query usd to execute very fast on MySQL 4.1.11 (in about less than 10 seconds). But on MySQL 5.0.22 it is taking an eternity. Running an EXPLAIN shows that an index_merge is being used as shown below in the EXPLAIN result. On MySQL 4.1.11 the the PRIMARY key was being used as the index as shown in the second query(The same query with FORCE index on PRIMARY). I am really confused as to why it is taking such a long time to execute when an index_merge is being used. As we can see that the number of row scans using index_merge is way too less when compared to the second query.

Why is the first query so slow when compared to the second one even if the number of rows to be examined is too less in the former?

Is this a bug in index_merge?

And we have atleast 15 such queries always running on the system. The server is clogged !!

Query with index_merge

# Execution time : 53 seconds
EXPLAIN
SELECT
SUM(1) AS ELE13,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 1, 1, 0 ) ) AS ELE14,
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) , 1, 0 ) ) AS ELE15,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4, 1, 0 ) ) AS ELE16,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3, 1, 0 ) ) AS ELE17,
SUM( IF( TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE18,
SUM( IF( TFMM.FLD_ISSUE_CLOSED != 3 AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 0, 1, 0 ) ) AS ELE19,
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 1, 1, 0 ) ) AS ELE20,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE21,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE22

FROM TBL_FORUMS_MSG_MAIN TFMM

WHERE TFMM.FLD_ACC_ID in (6)
AND TFMM.FLD_PARENT_ID = 0
AND TFMM.FLD_MSG_ID > 0
AND TFMM.FLD_MARK_AS_DELETED = 0
AND TFMM.FLD_BLOCK_STATE = 0
AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0
AND TFMM.FLD_ESCALATED_FLAG = 0
AND TFMM.FLD_ADD_DATE_TIME >= '2007-01-23 00:00:00'
AND TFMM.FLD_ADD_DATE_TIME <='2007-01-23 23:59:00'
ORDER BY TFMM.FLD_ADD_DATE_TIME
;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** row 1 ***
          table:  TFMM
          type:  index_merge
  possible_keys:  PRIMARY,FLD_PARENT_ID,FLD_ADD_DATE_TIME,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG
            key:  FLD_PARENT_ID,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG
        key_len:  4,2,2,2,2,2
            ref:  NULL
          rows:  10170
          Extra:  Using intersect(FLD_PARENT_ID,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG); Using where
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Query with FORCE INDEX(PRIMARY)

# Execution time : 13 seconds
EXPLAIN
SELECT
SUM(1) AS ELE13,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 1, 1, 0 ) ) AS ELE14,
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) , 1, 0 ) ) AS ELE15,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4, 1, 0 ) ) AS ELE16,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3, 1, 0 ) ) AS ELE17,
SUM( IF( TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE18,
SUM( IF( TFMM.FLD_ISSUE_CLOSED != 3 AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 0, 1, 0 ) ) AS ELE19,
SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 1, 1, 0 ) ) AS ELE20,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE21,
SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE22

FROM TBL_FORUMS_MSG_MAIN TFMM  FORCE INDEX(PRIMARY)

WHERE TFMM.FLD_ACC_ID in (6)
AND TFMM.FLD_PARENT_ID = 0
AND TFMM.FLD_MSG_ID > 0
AND TFMM.FLD_MARK_AS_DELETED = 0
AND TFMM.FLD_BLOCK_STATE = 0
AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0
AND TFMM.FLD_ESCALATED_FLAG = 0
AND TFMM.FLD_ADD_DATE_TIME >= '2007-01-23 00:00:00'
AND TFMM.FLD_ADD_DATE_TIME <='2007-01-23 23:59:00'
ORDER BY TFMM.FLD_ADD_DATE_TIME
;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** row 1 ***
          table:  TFMM
          type:  range
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4,
            ref:  NULL
          rows:  1059133
          Extra:  Using where
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[shoz]

[quote author=atitthaker link=topic=124679.msg516947#msg516947 date=1170150180]
Why is the first query so slow when compared to the second one even if the number of rows to be examined is too less in the former?

Is this a bug in index_merge?

And we have atleast 15 such queries always running on the system. The server is clogged !!
[/quote]

I wasn't aware of the "index_merge" type until your post and after reading the Mysql docs I don't think I have enough of an understanding to give the most accurate help. However, I'll make some assumptions and guesses.

[quote=http://dev.mysql.com/doc/refman/5.0/en/index-merge-intersection.html]
The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.
[/quote]

From that quote I can only guess that the query will take as much time as it takes to perform the longest of all the index scans. Meaning that If there's one comparison that requires a great deal more time to scan the relevant index than the others, then the query will take that much time.

As far as a solution is concerned, first let me quote the docs regarding index_merge

[quote=http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html]
Note: If you have upgraded from a previous version of MySQL, you should be aware that this type of join optimization is first introduced in MySQL 5.0, and represents a significant change in behavior with regard to indexes. (Formerly, MySQL was able to use at most only one index for each referenced table.)
[/quote]

Although the quote implies that this is only relevant to JOINs I think it's clear that it has to do with using indexes in general. With that in mind, the queries were written to be optimized for the behaviour of 4.1 and now it would appear that with 5.0s differences they'll have to be modified to regain the efficiency that was lost.

Also keep in mind the following

[quote=http://dev.mysql.com/doc/refman/5.0/en/explain.html]
Note that the rows column in the output from EXPLAIN is an educated guess from the MySQL  join optimizer
[/quote]

Now seeing how I really don't know the accuracy of what I've said about the "index_merge". if anyone has more information or think they have a better interpretation, feel free to correct anything I've said.

[fenway]

Not that I've used MySQL 5 yet, but I can see some issues here... you have a lot of indexes on column which I'm guessing have very low cardinality (like all of the flags).  If index merge simply takes all possible indexes, and tries to use them all, it's going to be very slow.