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

Regards

Edoardo Serra
WeBRainstorm S.r.l.

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
>
> Regards
>
> Edoardo Serra
> WeBRainstorm S.r.l.
>