How to let the query run on one partition

[Shawn Liu]

Hello,

I am using NDBCluster 7.1 and try to enhance system performance as high as possible.

I have one table TA as below,

create table TA(
USER_ALIAS varchar(100),
USER_ID varchar(100),
NAME varchar(50),
primary(USER_ALIAS)
) ENGINE=NDB;

USER_ALIAS and USER_ID is N:1, that is many USER_ALIAS maping to one USER_ID, e.g.
HAPPY-Boy --> 101010
North-Boy --> 101010
Locky-Boy --> 101010

The query ' select * from TA where USER_ID='101010' ' will run on all partitions, as the table is partitioned by USER_ALIAS which is the primary key.

What I want to do is to partition the table by USER_ID, so I do it as below,
create table TA(
USER_ALIAS varchar(100),
USER_ID varchar(100),
NAME varchar(50),
primary key (USER_ALIAS, USER_ID)
) ENGINE=NDB
partition by key(USER_ID);

then the query like
- select * from TA where USER_ID='101010'
- select * from TA where USER_ALIAS='HAPPY-Boy'
can run on just one partition.

However, MYSQL won't do that. To the query 'select * from TA where USER_ALIAS='HAPPY-Boy' would run on all partitions.

So, how can i do?

Thanks.

Alax