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


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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