Hi everybody,
I have a MySQL database with MyISAM tables.
As we're experiencing a lot of locking-related problems I decided to
migrate to InnoDB.
Our database is composed by a lot of small tables (1.000 - 10.000 rows)
and a huge table containing 7.000.000 rows, this big table is a sort of
a log of our subscriber's phone calls.
I have a query I often run on the big table that is performing really
poorly on InnoDB (18mins Innodb vs 29secs MyISAM)
This is my query
SELECT
DATE_FORMAT(calldate, '%d') AS day,
count(*) AS num,
disposition
FROM cdr
WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59'
GROUP BY day, disposition;
Using EXPLAIN I see that the query on the InnoDB table isn't using
indexes at all but the one on MyISAM table (same structure, same
indexes, same data) is choosing the correct index.
Here are my EXPLAIN results
MyISAM:
id: 1
select_type: SIMPLE
table: cdr
type: range
possible_keys: calldate,date-context-cause
key: calldate
key_len: 8
ref: NULL
rows: 697688
Extra: Using where; Using temporary; Using filesort
Innodb:
id: 1
select_type: SIMPLE
table: cdr_innodb
type: ALL
possible_keys: calldate,date-context-cause
key: NULL
key_len: NULL
ref: NULL
rows: 5035407
Extra: Using where; Using temporary; Using filesort
As you can see, Innodb doesn't use the calldate index (which seems to me
the correct choice)
Probably I can solve this query performance problem with an index on
calldate, disposition but I'd like to understand deeper the causes of
that to avoide re-analizing every query ad retry to optimize it as I did
with MyISAM.
I have got a Xeon quad core with SAS disks and 4 GB of RAM
I'm using a config file taken from MySQL sources optimized for innodb
and 4G RAM (my-innodb-heavy-4G.cnf)
I followed some simple optimization rules as putting InnoDB data dir on
a different array of disks on a different channel, etc...
Im using MySQL 5.0.32 on a Debian stable.
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]
