开发者

In mysql can I have a composite primary key composed of an auto increment and another field? Also, please critique my "mysql partitioning" logic

I am experimenting with mysql partitioning ( splitting the table up to help it scale better ), and I am having a problem with the keys on the table. First, I am using a python's threaded comments module... here is the schema

+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| content_type_id | int(11)          | NO   | MUL | NULL    |       |
| object_id       | int(10) unsigned | NO   |     | NULL    |       |
| parent_id    开发者_运维问答   | int(11)          | YES  | MUL | NULL    |       |
| user_id         | int(11)          | NO   | MUL | NULL    |       |
| date_submitted  | datetime         | NO   |     | NULL    |       |
| date_modified   | datetime         | NO   |     | NULL    |       |
| date_approved   | datetime         | YES  |     | NULL    |       |
| comment         | longtext         | NO   |     | NULL    |       |
| markup          | int(11)          | YES  |     | NULL    |       |
| is_public       | tinyint(1)       | NO   |     | NULL    |       |
| is_approved     | tinyint(1)       | NO   |     | NULL    |       |
| ip_address      | char(15)         | YES  |     | NULL    |       |
| id              | int(11)          | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

Note, I have modified this database by dropping the id col (primary by default), and re adding it.

Essentially, I want to have id AND content_type_id as my primary keys. I also want id to auto increment. Is this possible.

Second question. Since I am just learning about mysql partitioning, I am wondering if my partitioning logic is sound. There are 67 different content_types, and some (maybe all) of those content types allow comments to be made on them. My plan is to partition based on the type of object that is being commented on. For instance, the images will be commented on a lot, so I put any content type pertaining to images into one partition, and another content type that can be commented on is "blog entries", so there is a separate partition for that, and so on and so on. This will allow me to spread these partitions possibly to dedicated machines as the load grows. How is my understanding of this concept so far?

Thanks so much!


Since id will be auto incremented, it can be the primary key all by itself. Adding content_type to the primary key would not gain you anything in regards to the uniqueness of the key.

If you want to add an index for faster performance to the 2 columns, then add an alternate unique index to the table with the 2 columns instead of trying to add them both to the primary key. However, be aware that enforing uniqueness on the 2 columns would be a waste since id is already guaranteed to be unique by itself, so a regular index would make more sense if needed.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜