Re: Backup and trimming data idea
(All partitions are missing a paren; p405 has an additional syntax error.)
> So as the days grows I must keep adding more partitioning right?
Yes. Some hints...
I would add two partitions:
PARTITION start VALUES LESS THAN (0) -- for obscure reasons
PARTITION future VALUES LESS THAN MAXVALUE
1. Without it, if the next day script fails to run, there will be a place to put the data.
2. The script should REORGANIZE PARTITION future INTO pxxx..., future...
This will automatically fix the 'bug' mentioned in #1, if it ever happens.
I would make the script smart enough to decide whether it is time to run, then either do the ALTERs, or not. This way, if you accidentally run it twice, no harm is done. Example of harm: blindly DROPping the oldest PARTITION.
Other issues:
> course int(3)
Perhaps you meant SMALLINT UNSIGNED -- this would shrink the field from 4 bytes (INT) to 2 bytes (SMALLINT). Ditto for other over-sized fields.
> PRIMARY KEY (`mainDataID`),
"Every UNIQUE key must include the partition key", so
PRIMARY KEY (`mainDataID`, gpsDateTime),
I don't see the other INDEXes that I recommended.