Data not balanced in partitions

[Evan P]

Hi everybody,
I run MySQL 5.1.47 with partition plugin ACTIVE.

I create the following table:


CREATE TABLE IF NOT EXISTS `prova` (
`NE` varchar(8) NOT NULL,
`ASSERT` longtext NOT NULL
) ENGINE=MyISAM
PARTITION BY KEY(NE)
PARTITIONS 4;

Then I insert 4 rows:

INSERT INTO prova values ('AAA','this assert is from AAA');
INSERT INTO prova values ('BBB','this assert is from BBB');
INSERT INTO prova values ('CCC','this assert is from CCC');
INSERT INTO prova values ('DDD','this assert is from DDD');

I expect to find 1 record in each partition but:

mysql> explain partitions select * from prova where NE='AAA';
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | prova | p2 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select * from prova where NE='BBB';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | prova | p1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from prova where NE='CCC';
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | prova | p0 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select * from prova where NE='DDD';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | prova | p1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

So, question is:
what am I doing wrong? Why the 4 insert aren't splitted into the 4 partitions?
Why BBB and DDD went in the same partition?

Thank you very much for your help!
Evan



Edited 1 time(s). Last edit at 11/26/2011 05:42AM by Evan P.