Does above make sense to everyone? Let me approach this in a more abstract way:
Given following table structures:
session table
+--------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| affiliate_id | int(10) unsigned | NO | MUL | NULL | |
| publisher_id | int(10) unsigned | YES | | NULL | |
| created | datetime | NO | MUL | 0000-00-00 00:00:00 | |
+--------------+------------------+------+-----+---------------------+----------------+
action table
+--------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| session_id | int(10) unsigned | NO | MUL | 0 | |
| action_id | int(10) unsigned | NO | MUL | 0 | |
| action_order | tinyint(3) unsigned | NO | | 0 | |
| created | datetime | NO | | 0000-00-00 00:00:00 | |
+--------------+---------------------+------+-----+---------------------+----------------+
What is the most logical way to implement partitioning when 1:M relationship exists between session table and action table. 3 most common quries that need to be satisfied from least important to most important are:
select * from action where created >= 'xxxx-xx-xx' and created < 'xxxx-xx-xx';
select * from session where created >= 'xxxx-xx-xx' and created < 'xxxx-xx-xx';
select * from session as s left join action as a on s.id = a.session_id where a.created >= 'xxxx-xx-xx' and a.created < 'xxxx-xx-xx';
As mentioned in the first post, first 2 are not a problem since if I partition by range on created and query each table separately both statements will take advantage of pruning. It is the last query (with the join on field that is not part of partition expression) that's most important however. Even with index on a.session_id performance is poor. With range partitioning on both created fields, MySQL can't take advantage of pruning and does full table scan on the action table.
Your suggestions for an alternate setup are greatly appreciated.
Thanks again.