Mysql: performance partitions in joining tables

Question

I have a question with partitions and joining tables in MySQL. I have this sample schema:

CREATE TABLE A (
`ID` BIGINT NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_A_NAME` (`NAME`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE B (
`ID` BIGINT NOT NULL AUTO_INCREMENT,
`VALUE` double(22,6) DEFAULT NULL,
`A_REF` BIGINT DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_A_REF` (`A_REF`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE( A_REF ) (
                PARTITION p1000 VALUES LESS THAN (1000),
    PARTITION p2000 VALUES LESS THAN (2000),
    PARTITION p3000 VALUES LESS THAN (3000),
    PARTITION future VALUES LESS THAN MAXVALUE
                            );

The B partitioned table grows about 3 millon rows a month. The partition criteria by A_REF field is because B table is being accessed with queries like this (by now, it couldn't be changed):

SELECT B.VALUE
FROM A
INNER JOIN B ON A.ID = B.A_REF
WHERE A.NAME = 'James'

So, my questions are

• When executing EXPLAIN PARTITIONS... with this query, all partitions are accessed in EXPLAIN output. Does it mean that all partitions are actually accessed in the execution? Or is it only partition information?
• Does it help that partitioning criteria was the JOIN field?
• Does partitioning help in my example?
• Is there any other approach (another partitioning criteria, etc.) that help to access a lower number of rows or the query executes faster?

I'm worried about executing this query for a year with round 40 million of rows. So, any help is very very appreciated.

Answer 1

To speed up that query, get rid of partitioning.

The pruning failed (I think) because it did not have a constant with which to pick the partition. Instead, the JOIN reached into all partitions. All but one returned nothing, the one with the A_REF in it returned the row(s) just as if it had been a non-partitioned table.

There are many cases were partitioning does not help performance; you seem to have found one where it hurts.