Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm new to MySql partitioning and have a question regarding indexes. let say i have the following table:

CREATE TABLE `members` (
  `user_id` int(11) NOT NULL,
  `member_rating` int(11) DEFAULT '0',  
  `persona_id` int(11) NOT NULL,
  `high_value_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `user_id` (`user_id`,`persona_id`),
  KEY `member_rating_index` (`member_rating`),
  KEY `persona_index` (`persona_id`),
  KEY `high_value_members_index` (`user_id`,`high_value_type`)
/*!50100 PARTITION BY HASH (user_id)

to my understanding, everything will work fine as long as i query the table while providing user_id, my question is: how will the table perform when i'll make a query with out user_id, say something like:

SELECT * FROM members where persona_id=3

any ideas?

p.s. I'm running on MySql 5.1 / innodb

share|improve this question
up vote 1 down vote accepted

You would not be able to partition that table as you need to remove the unique key and make it a regular index instead.

To answer your question, if you query on column that isn't partitioned then MySQL would query every partition one-by-one until it answers the query. So if you query a partitioned table on a column that it is not partitioned on, you may get slower results then if the table was not partitioned.

That is why you need to choose the column you want to partition very carefully.

I would select a column that fits 80% of the most important use cases.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.