partition by varchar - random behavior?
Posted by:
mk mkrous
Date: January 02, 2016 02:07PM
Hello everyone,
I am trying to get familiar with MySQL Cluster Community Server (version:5.6.27-ndb-7.4.8-cluster-gpl) and I have the first question that puzzles me. I searched through the documentation and the forum but didn't find something relevant.
I have a very simple table on a cluster with 4 data nodes/partitions:
CREATE TABLE customer (
id int(10) NOT NULL ,
surname varchar(35) NOT NULL,
gender varchar(6) NOT null,
primary key(id, surname, gender)
)ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1 PARTITION by key (gender);
I therefore choose to have a partition by key on gender (it takes the values Male/Female). I insert 1000 rows and I want to see how they are distributed:
SELECT partition_name, table_rows
FROM information_schema.PARTITIONS
WHERE table_name='customer';
Results:
# partition_name, table_rows
'p0', '0'
'p1', '1000'
'p2', '0'
'p3', '0'
So all the rows go into a single partition.
However if I define gender as nvarchar(6) or varchar(40) the rows are distributed like I would expect in two partitions
# partition_name, table_rows
'p0', '493'
'p1', '0'
'p2', '507'
'p3', '0'
If I raise gender to varchar(60), all records go into a single partition.
If I raise it even more to varchar(100), the records are distributed between two partitions.
IS there any logic behind this or am I doing something completely wrong?
Thanks