Hi! Comments inline.
Edoardo Serra wrote:
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)
The rows returned in EXPLAIN SELECT (and SHOW TABLE STATUS) for InnoDB
tables is an estimate. For MyISAM, it is the actual number of rows in
the table. This is because InnoDB has to track a version for each row
in the table (for transactional isolation), and MyISAM does not, which
makes it much easier to just have a simple row count for the table.
This estimate of rows returned is what is used by the optimizer to
determine what execution plan is optimal for this particular query. In
this case, there are approximately 772K out of 5M rows which meet the
WHERE condition -- or about 15% of the total number of rows in the
table. There is a certain threshold, where above it the optimizer will
choose to do a sequential table scan of the data, versus do many random
seeks into memory or disk.
It seems that you are hovering around the threshold for where the
optimizer chooses to do a sequential table scan (InnoDB) vs a range
operation on a btree with lookups into the data file for each matched
row in the index (MyISAM). The difference in returning an estimate vs.
the actual row count *might* be the cause of the difference in execution
plans. Or, it could have something to do with the weights that the
optimizer chooses to place on bookmark lookups in MyISAM vs a quick
table scan in InnoDB. I'd be interested to see what the difference in
*performance* is? Also, in *either* engine, if you are executing this
particular query a *lot*, the best thing for you to do would be to put
the index on (calldate, usercost) so that you have a covering index
available to complete the query.
Cheers!
Jay
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]
