Yes, you're right, with that index query is flying...
then I used DAYOFMONTH(calldate) instead of DATE_FORMAT(calldate, '%d'),
it gives an extra performance gain of 5x
tnx for help
joe ha scritto:
U might want to try seting you index to calldate, disposition
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 25, 2007 10:03 PM
To: Edoardo Serra
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want to take a note on 4Gbytes
What kernel u use?
4Gbytes or bigger means nothing on your MySQL, because if your kernel is not
compiled using correct patch or simply use CentOS/RHEL, then your MySQl will
limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes.... is useless
On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
