"Using index" is somewhat good -- it means that it could satisfy the query (for this one table) by looking only in the index, not in the data.
"Using where" does not say much.
"Using filesort" is somewhat bad -- it means that it could not gather the desired rows in the desired order, but had to do a sort. "filesort" is a misnomer; there are times when it actually does it all in RAM. Still, it is an extra pass over the data.
Using MEMORY engine -- Sure you could use it. But don't (in this case). You would, as you point out, have to do something to persist the data. Note: regardless of the engine used, you have to scan 1.8M rows -- that takes a lot of work. You will still have a CPU spike.
Summary table...
You could collect the summary every minute (or 10 minutes, or hour, or whatever):
CREATE TABLE summary1 (
slot DATETIME NOT NULL COMMENT 'to the minute',
ct SMALLINT UNSIGNED NOT NULL COMMENT 'how many for that minute'
PRIMARY KEY (slot)
);
INSERT INTO summary1
select $starttime, # The 'minute' for this count
count(*)
from tblAuditLog
where pkAuditLogId > $start # Looks like you know the beginning/end
and pkAuditLogId < $end;
That would be a tiny cpu spike every minute.
Then your 'report' for one day would be
SELECT SUM(ct)
FROM summary1
WHERE slot >= $start_time
AND slot < DATE_ADD($start_time, INTERVAL 1 DAY)
This would also be a tiny cpu spike.
Uniques... Your original query did not do anything visible to comput "uniqueness"?? Or is each row a "unique transaction"? In Internet usage, "unique users" is a count of how many different (DISTINCT) users visited a site. Summary tables get messy -- a unique user for one minute or hour may be the same (not-unique) user the next minute or hour. Hence the SUM(ct) is not quite right.