Personally I'd use the date as your partition function, and partition by a hash of the year and month. Maybe splitting the data into 48 or more partitions. I've done this on some large volume databases and had good results.
ALTER TABLE `your_table`
PARTITION BY HASH(YEAR(`date_field`)*12 + MONTH(`date_field`))
PARTITIONS 48;
This should create a nice distributed set of data across 48 partitions (you may need to fiddle with the calculation on the date to get it quite right for your needs).
I build a model in Excel, with all the dates down one column, put the partition function on the second showing which partition that data would appear in. You can then chart the second column frequency to see how the data distribution is placed across the partitions - a really useful way of tinkering with your function before you alter your table!
Hope that helps...
You are right that mysql will check only one partition for a specific sender_id, but checks all the partitions for a specific receiver_id, as shown here:
mysql> explain partitions select * from messages where sender_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | messages | p0 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from messages where receiver_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | messages | p0,p1,p3 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
However, there are still benefits to that partitioning, depending on your hardware. When looking in all partitions for a receiver_id, mysql is really performing 3 select statements, one for each partition. It may be able to parallelize these select statements. Additionally, if you index receiver_id, it will be accessing 3, smaller, indicies.
In the end, you just have to do performance testing and see if it is paying off for your use case. Seeing as 100MB fits in RAM pretty easily these days, I wouldn't consider partitioning such a small table unless you have specific reasons to do so.
Best Answer
The use case of "purge after 30 days" is an excellent use of
PARTITIONing. (It is one of only 4 use cases that I know of.)Then every day do
Partition Details -- this is a follow-on to @jkavalik's "Mandatory reading", as @mootmoot provided.
Note: No subpartition. No partition by
id. No partition by account_id. The big advantage is making the big nightlyDELETErun instantly viaDROP PARTITION.You probably need some kind of index on
account_id. Consider a composite index.If you need to discuss this further, please provide
SHOW CREATE TABLE(with or without partitioning) and the main queries.