MySql – Global index

indexMySQLpartitioning

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

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

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

Best Answer

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.

Related Question