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.