MySQL Forums
Forum List  »  Partitioning

How to let the query run on one partition
Posted by: Shawn Liu
Date: January 09, 2013 08:29AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
How to let the query run on one partition
2410
January 09, 2013 08:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.