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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Thanks Ratheesh K J