Re: should i go with myisam or innodb?

[Rick James]

What you are summing over does not change, except that there are new rows to include, right? And existing rows are not deleted, right? If so, then do not sum over the whole table every time. Instead keep a summary table.

First of all, recommend an auto_increment id on the main table. This lets you keep track of where the summarization has 'left off'.

The summary table looks like
foo -- whatever key you need for the subtotal (could be multiple columns; often starts with a timestamp truncated to the, say, hour)
COUNT(*) as ct
SUM(...) as sum_...

Periodically do this:
1. Find the max id ($max_id) in the main table
2. INSERT INTO summary SELECT foo, COUNT(*), ... FROM main GROUP BY foo
WHERE id > $left_off AND id <= $max_id.
3. remember $max_id as $left_off

Then run your real query from the summary table, not the main table. It will be _much_ faster.

Assuming you have a UI that is prompting for the summarization, do two things:
1. In the UI, first run the summarization to catch up to the latest data.
2. As a CRON job (say, hourly), run the summarization. This way, the UI never has more than an hour's worth of data to summarize.

Use suitable LOCKs to prevent two summarizations from happening simultaneously. Keep $left_off in a table -- locking that table for WRITE may suffice for the lock.

Back to your question -- MyISAM or InnoDB. Count the disk hits. If your data is small enough to fit in RAM, there won't be much diff in perf. Note that InnoDB is 2x to 3x fatter than MyISAM. So, there comes a time when the data is small enough to be cached for MyISAM, but too big if InnoDB. Disk hits are costly. But... The summary table virtually eliminates this concern for size.

If you need AVG, do not save the average in the summary table. Instead save COUNT(*) and SUM(bar). Then calculate the average as SUM(sum_bar)/SUM(ct_bar) -- this is mathematically correct, and cheap enough to compute from the summary table.