MySQL Forums
Forum List  »  Partitioning

Hash/key Partition by something other than PK
Posted by: John Smith
Date: August 23, 2010 05:50AM

Is there any performance penalty or pit falls associated with hash/key partitioning by something other than the PK?

Say I have a table:

CREATE TABLE tweets (
tweet_id INT NOT NULL AUTO_INCREMENT,
author_id INT NOT NULL,
text CHAR(140) NOT NULL,
PRIMARY KEY (tweet_id)
)
PARTITION BY HASH(author_id)
PARTITIONS 12;

Will inserting new records into this table be any slower just because it is partitioned by author_id while the PK is a different field that is an auto-increment?

Also if I need to do a query like this:

SELECT *
FROM tweets
WHERE tweet_id=123

This query would require searching all partitions so it could be slow.

What if I modify the query into:

SELECT *
FROM tweets
WHERE tweet_id=123 AND author_id=33

The added condition "author_id=33" is unnecessary because the original condition "tweet_id=123" was enough to identify a unique record. But would this new query help MySQL to prune the partitions (only 1 partition satisfies the added condition of "author_id=33") and thus speed up the query?

Is MySQL capable of taking advantages of extra information like this to speed up query?

Options: ReplyQuote


Subject
Views
Written By
Posted
Hash/key Partition by something other than PK
3091
August 23, 2010 05:50AM


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.