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:

orderid - item_number_in_cart - price:

...

1000456 - 1 - 24.99

1000456 - 2 - 39.99

1000456 - 3 - 4.99

1000456 - 4 - 17.97

1000457 - 1 - 20.00

1000458 - 1 - 99.99

1000459 - 1 - 2.99

1000459 - 2 - 69.99

1000460 - 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.

The questions are:

• 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.

Thank you in advance.