How do I build index for MySQL table that inserts a lot and queries only most recent data I have a MySQL database table using InnoDB and looks like this:

OrgID (INTEGER)
MachID (INTEGER)
Date (DATETIME)
IdleTime (INTEGER)


I do two operations on this table:


Insert a new record, with Org ID, Mach ID, Date and IdleTime
Query for either a given Organization/Machine ID pair, for last day, the sum of IdleTime:


SELECT SUM(IdleTime) FROM DBTable WHERE OrgID=1 AND MachID=2 AND Date BETWEEN 'YYYY-mm-dd 00:00:00' AND 'YYYY-mm-dd 23:59:59';

We may want to get the sum of total IdleTime for given OrgID, but it is just an extra bonus. In most cases, YYYY-mm-dd is yesterday.

We concern more on the performance ofINSERT operation but we do not want too slow SELECT. My boss recommended adding a new column as the primary key, namely something like INSERT_TIME(TIMESTAMP) , to ensure insertion is sequential. I am wondering if using a PRIMARY KEY (OrgID, MachID, Date) may help. Any suggestions on how do I use the index to boost the performance?
PRIMARY KEY (OrgID, MachID, Date) would be good for that query.  But, a PRIMARY KEY is necessarily "unique"; is that combination unique?  If not unique, then make it a plain INDEX and have something else as the PRIMARY KEY.

If we are talking about millions of rows per day, then Summary tables would be worth doing.

If it is usually "yesterday", then consider:

AND Date &gt;= CURDATE() - INTERVAL 1 DAY
AND Date  &lt; CURDATE()


Or, more generically, (where you insert the days ago for '?'):

AND Date &gt;= CURDATE() - INTERVAL ?   DAY
AND Date  &lt; CURDATE() - INTERVAL ?-1 DAY


More

Insuring sequential -- not that important.  With that 3-part PK, you will be inserting sequentially in several spots, namely each combination of OrgID and MachID.

Insert do need to update secondary keys, but this is usually not a big deal.  (To get really technical, see "InnoDB's change buffering".)

If you are going to be inserting more than 100 rows per second, then I recommend gathering them up and batch inserting.  Also, see innodb_flush_log_at_trx_commit = 2.