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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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` (
  `Date` datetime DEFAULT NULL,  
  `GpsLatitude` double DEFAULT NULL,
  `GpsLongitude` double DEFAULT NULL,
  `DeviceID` int(11) NOT NULL,  

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

Update: The queries are like

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

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


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.