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.
>
