Take the 2-minute tour ×

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

1 Answer 1

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


