joe schrieb:
> U might want to try seting you index to calldate, disposition
or calldate, day, disposition ...
and depending on your MySQL version:
(to circumvent possible limitations in InnoDB with your MySQL version)
you could try
WHERE calldate >= '2007-07-01 00:00:00'
AND calldate <= '2007-07-30 23:59:59'
or
SELECT
DATE_FORMAT(calldate, '%d') AS day,
count(*) AS num,
disposition
FROM (
SELECT
DATE_FORMAT(calldate, '%d') AS day,
num,
disposition
FROM
cdr
WHERE
calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59'
)
GROUP BY day, 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]
