Question

Each member has 0 or more orders. Each order contains at least 1 item. memberid - varchar, not integer - that s OK (please do not mention that s not very good, I can t change it). So, thera 3 tables: members, orders and order_items. Orders and order_items are below:

CREATE TABLE `orders` (
`orderid` INT(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
`memberid` VARCHAR( 20 ),
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`info` VARCHAR( 3200 ) NULL ,
PRIMARY KEY (orderid) ,
FOREIGN KEY (memberid) REFERENCES members(memberid)
) ENGINE = InnoDB;


CREATE TABLE `order_items` (
`orderid` INT(11)  UNSIGNED NOT NULL,
`item_number_in_cart` tinyint(1) NOT NULL , --- 5 items in cart= 5 rows
`price` DECIMAL (6,2) NOT NULL,
FOREIGN KEY (orderid) REFERENCES orders(orderid)
) ENGINE = InnoDB;

So, order_items table looks like:

<斯特罗内> - 项目编号 - 价格:

............

2.1000456 - 1 - 24.99

10.00456 - 2 - 39.99

10.00456 - 3 - 4.99

10.00456 - 4 - 17.97

1000457 - 1 - 20.00

10.00458 - 1 - 99.99

3.1000459 - 1 - 2.99

1000459 - 2 - 69.99

10.00460 - 1 - 4.99

............

As you see, order_items table has no primary keys (and I think there is no sense to create an auto_increment id for this table, because once we want to extract data, we always extract it as WHERE orderid= 1000456 order by item_number_in_card asc - the whole block, id woudn t be helpful in queries). Once data is inserted into order_items, it s not UPDATEd, just SELECTed.

问题是:

I think it s a good idea to put index on item_number_in_cart. Could anybody please confirm that?
Is there anything else I have to do with order_items to increase the performance, or that looks pretty good? I could miss something because I m a newbie.

事先感谢你。

Answer 1

主要钥匙可以跨越多个栏目。您可以使用<代码>PRIMear的栏目来做这项工作,但你可以用多个栏目确定一个单独的主轴:

CREATE TABLE `order_items` (
    `orderid` INT(11)  UNSIGNED NOT NULL,
    `item_number_in_cart` tinyint(1) NOT NULL , --- 5 items in cart= 5 rows
    `price` DECIMAL (6,2) NOT NULL,
    PRIMARY KEY (orderid, item_number_in_cart),
    FOREIGN KEY (orderid) REFERENCES orders(orderid)
) ENGINE = InnoDB;

此外,一个首要的关键只是一个独特的关键,即每个栏目不以某种名称无效;你可以为非致命的栏目创造自己的独特钥匙,以便产生同样的效果。

You ll not likely get much of a performance improvement by indexing item_number_in_cart; as the number of line items for a given order will tend to be small, sorting by item_number_in_cart won t take much time or memory. However, including the column in a primary key will help with data consistency.

Answer 2

Index on item_number_in_cart won t be used. It s tiny int, not selective enough, and won t even considered by the engine once you have 2 records. You can add it as a second column to the existing index on orderid (since you created FK constraint on orderid, mysql automatically adds an index on this field).
You say that data in order_items never updated, but I think it can be deleted; doing so without primary key will be problematic.

Answer 3

整整,我拥有一条自动通道,因为我是一大信仰者,可以 sur取钥匙,但正如第07号法律所建议的那样,如果说是某种指数,甚至是秩序的关键,那么项目编号——_in_cart,就应当划出。注按项目编号排列的次序将采用两种换算率(以数据为单位,然后按数字顺序排列),这样,指数/关键数字就能够直截了当,因此,即使有代用钥匙,你也希望这一指数。

友情链接