Support for Partitions

[raghupradeep]

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.

[raghupradeep]

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.

[przemek]

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:
[url]https://dev.mysql.com/doc/refman/5.7/en/explain.html[/url]
In PXC 5.7, it works the same way.

[Timur_Solodovnikov]

@raghupradeep;48023

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)