Perhaps some clues here: I started taking the problem query apart to see
what slows things down. I found a culprit, but I don't understand:
mysql> select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN
'2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0;
+----------+
| count(*) |
+----------+
| 437 |
+----------+
1 row in set (0.35 sec)
mysql> select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN
'2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0 and RuleLimit >=0;
+----------+
| count(*) |
+----------+
| 437 |
+----------+
1 row in set (6 min 15.93 sec)
Explain says that very few rows are being examined, but it takes a very
long time.
mysql> explain select count(*) from Crumb where customer_id=380 and Actual_Time
BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0\G
--------------
explain select count(*) from Crumb where customer_id=380 and Actual_Time
BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0
--------------
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Crumb
type: range
possible_keys:
ix_crumb_custid_actualtime,ix_crumb_errorcode,ix_Crumb_on_Actual_Time,ix_Crumb_on_ErrorCode_RuleLimit
key: ix_crumb_custid_actualtime
key_len: 12
ref: NULL
rows: 290
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select count(*) from Crumb where customer_id=380 and Actual_Time
BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0 and
RuleLimit >=0\G
--------------
explain select count(*) from Crumb where customer_id=380 and Actual_Time
BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0 and
RuleLimit >=0
--------------
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Crumb
type: ref
possible_keys:
ix_crumb_custid_actualtime,ix_crumb_errorcode,ix_Crumb_on_Actual_Time,ix_Crumb_on_ErrorCode_RuleLimit
key: ix_Crumb_on_ErrorCode_RuleLimit
key_len: 5
ref: const
rows: 38
Extra: Using where
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
