Adding new key(s) to enable partitioning, INSERT performance? 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&gt; '2010-01-01') AND (carlocation.Date&lt; '2010-01-02'))


Always quering for ONE device only, in one or multiple days.
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.