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] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
