Minor items:
Any two of these can be used to calc the third:
count(*) TotalRequests,
sum(if(fldResponseType=true,1,0)) SuccessResponse,
sum(if(fldResponseType=false,1,0)) AbortsAndErrors,
Suggest keeping only two of them.
I suspect true is 1 and false is 0:
sum(if(fldResponseType=true,1,0)) -->
sum(fldResponseType)
date('2009-03-04') -->
'2009-03-04'
OK, back to discussing the summary table. You are almost there!
* Don't build the summary table all at once (except to get started).
* You have an AUTO_INCREMENT PK on the table tblAuditLog, right?
* You can remember "where you left off" using that PK.
* To the SELECT ... GROUP BY ... add a WHERE clause restricting the PK to pick up where you left off. It becomes "INSERT INTO summary SELECT ..."
* That will make each hourly SELECT run in a fraction of the time you quote. Sure, it will be a filesort, but it won't be that long.
* The report will have to SUM the sums, SUM the COUNTs, do SUM(sum...)/SUM(count...) to get an average, etc.
* The PK is all you need, no extra indexes.
* Don't worry about getting exactly one hour's worth in each pass; the report will take care of the dups of (RequestHour, ShortCode); instead, concentrate on "left off". (This deals with your concern about the db being down.)
* Events? If you are on Unix, just use cron. Maybe set it up to run every 10 minutes (*/10 * * * *).
More discussion...
http://forums.mysql.com/read.php?125,252723,253118
http://forums.mysql.com/read.php?10,252593,252740
http://forums.mysql.com/read.php?125,245133,245357