mysql> SELECT sum(int_field) FROM testdb WHERE dt_receipt BETWEEN '2007-09-12' AND '2007-10-12';
+----------------+
| sum(int_field) |
+----------------+
| 11484757 |
+----------------+
1 row in set (0.32 sec)
mysql> explain SELECT sum(int_field) FROM testdb WHERE dt_receipt BETWEEN '2007-09-12' AND '2007-10-12';
+----+-------------+-------------+-------+----------------+-----------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+----------------+-----------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | testdb | index | IDX_DTR_CD_IDA | IDX_CDA_DTR_AMT | 12 | NULL | 148036 | Using where; Using index |
+----+-------------+-------------+-------+----------------+-----------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
위의 sum()함수를 사용하는 쿼리가 0.32sec나 소요됩니다. 이 보다 더 빠르게 할 순 없을까요?
dt_receipt는 date형입니다.
index를 타는데도, rows에 148036나 되어서 그런 것 같습니다.
|