The latest edition of the High Performance MySQL book has this to say about paritioning a large table where all the hot data is clustered temporally:
Suppose you have a table with an autoincrementing idprimary key, but you want to partition the data temporally so the “hot” recent data is clustered together. You can’t partition by a timestamp column unless you include it in the primary key, but that defeats the purpose of a primary key. You can partition by an expression such as HASH(id DIV 1000000), which creates a new partition for each million rows inserted. This achieves the goal without requiring you to change the primary key. It has the added benefit that you don’t need to constantly create partitions to hold new ranges of dates, as you’d need to do with range-based partitioning.
I had never thought a partitioning scheme like this but I love the fact that it has uniform sized parititions and works with a very small primary key on a single field instead of needing to make the PK timestamp + some other identity col
My question is if a typical query on a table like this looks something like:
SELECT sensor_id, sensor_value FROM measurements
WHERE sensor_date >= '2013-01-01 10:00:00'
I don't see how the partition index is going to automatically help me hit only the last partition unless I add something like:
WHERE ... AND HASH(id DIV 1000000) = <max partition #>
Or even
WHERE .. AND id between 3000000 and 4000000
To the original query. Or am I missing something? What if I want to go back in time far enough and hit another partition (perhaps for a historical query?). How do I setup my temporal queries to support finding a record acorss the parititions using this partition scheme?
