1

Are there any known pitfalls with MySQL partitioning when server behavior differs a lot from the expected one?
I'll try to explain what I mean. Let's create partitioned table and fill it with random data.

CREATE TABLE PartitionedTable (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int,
  PRIMARY KEY (id),
  KEY(col)
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (4000),
    PARTITION p2 VALUES LESS THAN (6000),
    PARTITION p3 VALUES LESS THAN (8000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
    );

INSERT INTO PartitionedTable (col)
SELECT FLOOR(RAND() * 1000)  AS col FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4 ;

Now let's check the following queries (I'll use MySQL to be able to use EXPLAIN ANALYZE but it seems execution plan for other MySQL Server versions is the same). The first query:

EXPLAIN ANALYZE SELECT col FROM PartitionedTable WHERE id >= 6000 AND id < 8000 ORDER BY col DESC LIMIT 1;

Execution plan:

-> Limit: 1 row(s)  (actual time=0.021..0.021 rows=1 loops=1)
    -> Filter: ((PartitionedTable.id >= 6000) and (PartitionedTable.id < 8000))  (cost=0.10 rows=1) (actual time=0.020..0.020 rows=1 loops=1)
        -> Index scan on PartitionedTable using col (reverse)  (cost=0.10 rows=1) (actual time=0.019..0.019 rows=1 loops=1)

Everything looks ok, server found necessary partition (you can see it if you run EXPLAIN without ANALYZE) and read one last row from index on col column.

The second query (which requests the same data but in a bit different way):

EXPLAIN ANALYZE SELECT MAX(col) FROM PartitionedTable WHERE id >= 6000 AND id < 8000;

Execution plan:

-> Aggregate: max(PartitionedTable.col)  (actual time=0.938..0.938 rows=1 loops=1)
    -> Filter: ((PartitionedTable.id >= 6000) and (PartitionedTable.id < 8000))  (cost=400.26 rows=2000) (actual time=0.037..0.799 rows=2000 loops=1)
        -> Index range scan on PartitionedTable using PRIMARY  (cost=400.26 rows=2000) (actual time=0.035..0.570 rows=2000 loops=1)

Now server found the same partition but decided to scan all data inside. I know that my example is artificial (I partitioned table by primary key and used conditions which exactly match partition borders) but this execution plan looks pretty inefficient and strange because server already has an index with properly sorted data and it was able use it in a corresponding way executing the first query.

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f27c4b395b8f02a2972cc0ae306fe1cf

Improve this question

2 Answers 2

Reset to default
1

There are lots of pitfalls.

  • Simply adding PARTITIONing will not improve performance.
  • Change most of the indexes if you add PARTITIONing.
  • Don't use unless you have a least a million rows
  • Only BY RANGE() is useful.
  • It is essentially useless to PARTITION BY RANGE(the-primary-key)
  • There are only 4 use case for partitioning.
  • A PARTITIONed table is likely to be bigger than the un-partitioned equivalent.
  • UNIQUE and FOREIGN KEY are virtually unavailable.

More: http://mysql.rjweb.org/doc.php/partitionmaint

SELECT  col
    FROM  PartitionedTable
    WHERE  id >= 6000
      AND  id  < 8000
    ORDER BY  col DESC
    LIMIT  1;

Sure, there is some partitioning pruning. That is followed by a range scan in each picked sub-table. Scanning a BTree is possibly faster without the pruning on top of it.

INDEX(key) has (silently) id tacked on the end. So, this is an "index range scan" with or without partitioning. But partitioning slows it down because it must do a sort if the results are not in a single partition. (Change that id range to see how much worse things get.)

"Test case error": SELECT MAX(col) .. WHERE id ... cannot be optimized, with or without partitioning. Furthermore, the Optimizer in 5.5 performs the query more efficiently.

I ran your queries on several versions of MySQL/MariaDB. For the first query:

Partitioned:       0.5 to 1.7 ms
Not partitioned:   0.4 to 0.6 ms

Need I say more?

Improve this answer
2
  • Thanks for reply but I tried to ask about a bit different thing. I don’t try to use partitioning to improve queries performance and I'm not a fan of partitioning at all. It's obvious that people need to revise indexes when start using partitioning. I understand that query from my artificial example cannot be optimized and works in different way if we change conditions range. We had a "long" discussion with RolandoMySQLDBA below his answer where I explained what is the difference for me and why I call it unexpected behavior.
    – NikitaSerbskiy
    Jun 26, 2020 at 9:09
  • The point that foreign and unique keys are unavailable is the part of the answer that most closely matches to what I would like to see in answers (despite this is described in MySQL documentation)
    – NikitaSerbskiy
    Jun 26, 2020 at 9:09
0

Unfortunately, one major pitfall is not understanding what partitioning does.

Please note that you partitioned the PRIMARY KEY (id). That's what the index does without partitioning. You would be much better off partitioning the col column.

Please change this

CREATE TABLE PartitionedTable (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int,
  PRIMARY KEY (id),
  KEY(col)
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (4000),
    PARTITION p2 VALUES LESS THAN (6000),
    PARTITION p3 VALUES LESS THAN (8000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
INSERT INTO PartitionedTable (col)
SELECT FLOOR(RAND() * 1000)  AS col FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4 ;    

EXPLAIN ANALYZE SELECT col FROM PartitionedTable WHERE id >= 6000 AND id < 8000 ORDER BY col DESC LIMIT 1;
EXPLAIN ANALYZE SELECT MAX(col) FROM PartitionedTable WHERE id >= 6000 AND id < 8000;

to this

CREATE TABLE PartitionedTable (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int,
  PRIMARY KEY (id,col),
  KEY(col)
) PARTITION BY RANGE(col) (
    PARTITION p0 VALUES LESS THAN (200),
    PARTITION p1 VALUES LESS THAN (400),
    PARTITION p2 VALUES LESS THAN (600),
    PARTITION p3 VALUES LESS THAN (800),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
INSERT INTO PartitionedTable (col)
SELECT FLOOR(RAND() * 1000)  AS col FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4 ;

I just changed it in dbfiddle.

Note that the PRIMARY KEY must include the column you wish to partition by.

If you execute these queries,

SELECT COUNT(1),MIN(col),MAX(col) FROM PartitionedTable PARTITION (p0);
SELECT COUNT(1),MIN(col),MAX(col) FROM PartitionedTable PARTITION (p1);
SELECT COUNT(1),MIN(col),MAX(col) FROM PartitionedTable PARTITION (p2);
SELECT COUNT(1),MIN(col),MAX(col) FROM PartitionedTable PARTITION (p3);
SELECT COUNT(1),MIN(col),MAX(col) FROM PartitionedTable PARTITION (p4);

You will see a fair spread of col values in each partition.

You will need to partition based on a key that is tied to a PRIMARY KEY or unique column sequence.

Here are my past posts on Partitioning:

Of course, the best resource to read is always the MySQL Documentation on Partitioning.

Improve this answer
7
  • I partitioned table on primary key just for example. Please see these queries with your table definition: EXPLAIN SELECT id FROM PartitionedTable WHERE col >= 600 and col < 800 ORDER BY id DESC LIMIT 1; EXPLAIN SELECT MAX(id) FROM PartitionedTable WHERE col >= 600 and col < 800; We have the same issue described in my question - MySQL creates inefficient execution plan for the first query while it should create the same plan as for the second query or for this one - EXPLAIN SELECT MAX(id) FROM PartitionedTable PARTITION (p3);
    – NikitaSerbskiy
    Jun 24, 2020 at 23:11
  • YOUR QUERY : If you replaceEXPLAIN ANALYZE with EXPLAIN, you will quickly a full table scan against partition p3 only. This is the correct plan because you chose the entire range of p3.
    – RolandoMySQLDBA
    Jun 25, 2020 at 1:59
  • Try creating the table without any partitions. You should get the same bad plans If you create any table, partitioned or not, just to do range scans against the PRIMARY KEY, chances are, you are going to get nothing but scans, especially if you choose large ranges. In your case, it will be full table scans against one or more partitions.
    – RolandoMySQLDBA
    Jun 25, 2020 at 2:09
  • the issue is - why tthis query: SELECT MAX(id) FROM PartitionedTable WHERE col >= 600 and col < 800 uses table scan? while I expect it should work like this one: SELECT MAX(id) FROM PartitionedTable PARTITION (p3); or this one: SELECT id FROM PartitionedTable WHERE col >= 600 and col < 800 ORDER BY id DESC LIMIT 1;
    – NikitaSerbskiy
    Jun 25, 2020 at 7:56
  • Please run EXPLAIN SELECT id FROM PartitionedTable WHERE col >= 600 and col < 800 ORDER BY id DESC LIMIT 1; The output shows that all 5 partitions are examined. Why ? The Query Optimizer will not treat a partition the same as a regular table. Each partition has its own set of index statistics separate and distinct from other partitions. Before index statistics are used, the partitions involved must be determined first. Each partition does not have an independent PRIMARY KEY .Therefore, navigating a PRIMARY KEY for a MAX value within a partition becomes impossible.
    – RolandoMySQLDBA
    Jun 25, 2020 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.