0

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

0

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 | |
  • 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. – Nikita Jun 26 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) – Nikita Jun 26 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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