Partition Or Separate Table

Answer 1

definitely use partitioning. why go to all of the hassle to figure out which table to use to answer a question when mysql will do it for you? and good luck find the current location of all of your trucks if you're not using partitioning!

1. partitioning gives you the performance benefits of multiple tables, but with automatic pruning (selection of just the tables needed to answer the query).

2. nothing is ever "best". the question is: what is best for your problem?

3. this is impossible to answer. you will just have to monitor your system for performance issues and adjust server settings or scale as necessary.

4. at least as far as mysql is concerned, none as good as partitioning!

Answer 2

There should be very little performance difference between making a separate table for each vehicle, and making the vehicle ID the first field in the primary key. You get the same grouping on disk either way, and mysql should have no trouble with millions of rows in a table.

Partitions are only useful if you have multiple disks on your machine and want to spread the load across disks.

So I guess my answer is do neither. Designing this in a priori seems overkill.

Answer 3

Don't bother with partitioning for 28,800 rows per day.

We don't (yet) with over 5 million per day. (The "yet" means we have no business input on what data retention policy they want)

Answer 4

One thing I want to point out is that having one table (which you can partition later when you need to) will be much easier to maintain both in the database and in terms of querying the data.