MySQL: optimization of table (indexing, foreign key) with no primary keys
- by Haradzieniec
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.
