Hi guys,
I'm moving a database to InnoDB because I need some transaction related
features but I'm having big problems with perrformances.
I have a big table with 5mln rows on which I need to run some SELECTs.
It's the Call Detail Record of a telco, so each record has a 'calldate'
field with an index on it (it's a non unique index)
I have the same table in InnoDB and MyISAM storage engines.
I have this simple query:
SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01
00:00:00' AND '2007-06-30 23:59:59'
If I run it on the MyISAM table, MySQL choose the right index (the one
on the calldate column) and the query is fast enough
If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN
query tells me that 'calldate' is between the available indexes
Here are my EXPLAIN results
mysql> EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate
BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59';
+----+-------------+-------+------+-----------------------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL |
NULL | NULL | 5016758 | Using where |
+----+-------------+-------+------+-----------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate
BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59';
+----+-------------+-------+-------+-----------------------------+----------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------+----------+---------+------+--------+-------------+
| 1 | SIMPLE | cdr | range | calldate,date-context-cause |
calldate | 8 | NULL | 772050 | Using where |
+----+-------------+-------+-------+-----------------------------+----------+---------+------+--------+-------------+
1 row in set (0.11 sec)
Another strange thing is that the EXPLAIN on InnoDB says the table has
5016758 rows but a SELECT count(*) returns 4999347 rows (which is the
correct number)
Tnx in advance for help
Regards
Edoardo Serra
WeBRainstorm S.r.l.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
