Adding new key(s) to enable partitioning, INSERT performance?

Question

I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was thinking of create a partition in the table by Date or DeviceID.

In that case I will need to add the partitioning keys as primary keys?, how will that affect the insertion performance?

CREATE TABLE `carlocation` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Date` datetime DEFAULT NULL, 
 `GpsLatitude` double DEFAULT NULL,
 `GpsLongitude` double DEFAULT NULL,
 `DeviceID` int(11) NOT NULL, 
 PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The server is a Dual Core Xeon with 2GB RAM. Thanks.

Update: The queries are like

SELECT *
FROM carlocation
 WHERE ((carlocation.DeviceID = 2) AND (carlocation.Date> '2010-01-01') AND (carlocation.Date< '2010-01-02'))

Always quering for ONE device only, in one or multiple days.

• András Váczi: Very much depends on how your typical query looks like. Could you tell us?
• D.Rosado: True, updated question.

Answer 1

Your one select needs this "compound" index.

INDEX(DeviceID, Date)

Do you have other queries?

But your question was about PARTITIONing. No advantage, based on what you have said so far.