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
