Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Very much depends on how your typical query looks like. Could you tell us? –  dezso Jun 26 '12 at 12:37
    
True, updated question. –  D.Rosado Jun 26 '12 at 12:49

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.