CODEWITHSUNDEEP
mysqldatabasequery-optimizationpartitioningdatabase-partitioning
yaki_nuka
yaki_nuka

Reputation: 804

Mysql: performance partitions in joining tables

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

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

Upvotes: 1

Views: 995

Answers (1)

Rick James
Rick James

Reputation: 142356

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.

Upvotes: 1

Related Questions