If you are going to do
where day=26
you should have
INDEX(day)
Without the WHERE, you are stuck with scanning your big table, and fetching info from the smaller one. Currently it is fetching 7M (or whatever) rows form the big table and another 7M probes into the smaller. We can shrink the second number to 2K, thereby doubling the speed (perhaps):
Before:
select count(*), D.sev, D.msgNum, D.SDS_loghost,
A.aCtion
from syslogD D
left join syslogDAlert_data A on A.msgID = D.msgNum
group by D.msgNum;
After:
select X.ct, X.sev, X.msgNum, X.SDS_loghost,
A.aCtion
from
( select count(*) as ct, D.sev, D.msgNum, D.SDS_loghost
from syslogD D
group by D.msgNum
) X
left join syslogDAlert_data A on A.msgID = X.msgNum
I still think the GROUP BY is "wrong". Which "sev" will you get?
The next performance boost will be very big -- but complicated. You would need to build daily "summary" info, and put it into a different table.