MySQL Forums
Forum List  »  Partitioning

Re: Partitioning and JOIN
Posted by: m z
Date: November 06, 2009 11:15AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
7444
m z
November 05, 2009 02:07PM
Re: Partitioning and JOIN
3259
m z
November 06, 2009 11:15AM
3001
November 06, 2009 08:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.