Re: InnoDB CPU Spikes

[Aftab Khan]

>Looks like the sorting is eating the CPU, also the explain shows "Using filesort", I am unable to add an index on the date column because it slows down my inserts and ultimately my >application crashes. The pk Id that I used was based on some query that I manually ran to get them, the application has no knowledge on these values.

### SQL
select hour(fldCreatedDate) RequestHour, fldshortcode as ShortCode, count(*) TotalRequests, sum(if(fldResponseType=true,1,0)) SuccessResponse, sum(if(fldResponseType=false,1,0)) AbortsAndErrors, count(distinct fldMsisdnnumber) TotalUniqueUsers from tblAuditLog where date(fldCreatedDate) = date('2009-03-04') group by RequestHour, ShortCode;

You have mentioned when you add an index on fldCreatedDate then it slows down your inserts. try to do batch inserts. if you don't add index on fldCreatedDate then it does slow down your Selects. Remember you can't get rid of filesort by adding index on `fldCreateDate`. Also if you do/did have index on `fldCreateDate` it wont be used because you have used date() function.

you have setup summary tables, which is good, do you archive the data after creating summaries?



Edited 1 time(s). Last edit at 04/01/2009 10:02AM by Aftab Khan.