Not the answer you need?
Register and ask your own question!

Support for Partitions

raghupradeepraghupradeep ContributorCurrent User Role Beginner
I am just exploring XtraDB cluster for a production rollout and is confused about the table partitioning support it provides. By the way the version that I am using is 5.6. I created a table with range partition and loaded data. When I am explaining the select, it is not showing the partitions which will be used. The same query on MySQL normal database is showing me the partitions which will be used to fetch the data.

On checking filesystem I do can see the partitions. But, not able to see it in explain plan with XtraDB. Is there any setting to tweak ? Or is it actually using partitions ? Was not able to find any documentation which talks about partition support on XtraDB cluster.

Comments

  • raghupradeepraghupradeep Contributor Current User Role Beginner
    I think,there is a small difference in the output resultset from MySQL's explain and explain of XtraDB cluster. In MySQL's explain it shows the partitions used to get the data. To get similar output, using xtraDB cluster we have to use explain partitions. So, in short partitions are used which solves my problem.
  • przemekprzemek Percona Support Engineer Percona Staff Role
    Yes, it doesn't matter if the table is partitioned from the PXC replication point of view, it's handled on InnoDB level.

    Regarding the EXPLAIN, please note that by default, information about partitions is enabled in the output since MySQL 5.7:
    https://dev.mysql.com/doc/refman/5.7/en/explain.html
    In PXC 5.7, it works the same way.
  • Timur SolodovnikovTimur Solodovnikov Entrant Current User Role Beginner
    I am just exploring XtraDB cluster for a production rollout and is confused about the table partitioning support it provides. By the way the version that I am using is 5.6. I created a table with range partition and loaded data. When I am explaining the select, it is not showing the partitions which will be used. The same query on MySQL normal database is showing me the partitions which will be used to fetch the data.

    On checking filesystem I do can see the partitions. But, not able to see it in explain plan with XtraDB. Is there any setting to tweak ? Or is it actually using partitions ? Was not able to find any documentation which talks about partition support on XtraDB cluster.

    If I understood you issue correctly, please try to use "explain partitions [your query]"":

    mysql> EXPLAIN partitions select 1;
    +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
    +----+
    +
    +
    +
    +
    +
    +
    +
    +
    +
    +
    1 row in set (0.20 sec)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.