Mysql partitioning and joins

Question

Sorry if this is a stupid question but new to this so need some help to understand a couple of things. Im currently upgrading mysql to 5.1 so I can use partitions in mysql. My question is if I partition a table, would a partitioned table, including the pruning process, still work on a quering using a join or is partitioning optimal if your just quering the table that has the partitions?

EDIT

Here is an example query:

SELECT event.*,site.* FROM event INNER JOIN site ON event.siteid = site.id
WHERE event.eventdate >= [somedate] AND event.eventdate <= [somedate]
AND event.siteid = [siteid]

And I have partitions setup on the events table using the eventdate field. Would mysql still be able to use the partitions on the events table including the pruning process?

Answer 1

Partioned tables can be used in joins.

Tweak the where clause to include one partition only to get the best performance.
e.g. if you partition by year, you can do a join like:

select * from a
inner join partioned_table p on (p.id = a.id)
where p.year = '2011';

This query will work with and without the where clause, but with the where clause it will be much faster because you are only accessing one partition.
If you access more partitions, MySQL has to use a temporary table to stitch the partitions together before it can do the join.
This kind of defeats the purpose of partitions.

Answer 2

Not sure I understand the question well, but the partitioning should not affect the way your joins work. It just affects the way the data is stored. MySQL engine will take care of knowing where to fetch the data, so in reality you should keep your joins just the way you have them. Here is an example:

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

select a.* 
from members a, subscriptions b 
where a.email = b.email and b.generation='X' 
  and a.joined between '1980-01-01' and now()

Let me know if that makes sense! Marcelo