CODEWITHSUNDEEP
mysqlperformanceoptimizationindexingprimary-key
Haradzieniec
Haradzieniec

Reputation: 9338

MySQL: optimization of table (indexing, foreign key) with no primary keys

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:

Thank you in advance.

Upvotes: 0

Views: 1148

Answers (3)

outis
outis

Reputation: 77420

Primary keys can span multiple columns. You can't use the PRIMARY attribute of columns to do this, but you can define a separate primary key with multiple columns:

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;

Moreover, a primary key is simply a unique key where every column is not null with a certain name; you can create your own unique keys on non-nullable columns to get the same effects.

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.

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Well I'd be having an autoinc anyway, as I'm a big believer in surrogate keys, but as suggested by alex07 an index, or even primary key of orderid,item_number_in_cart should sort things out. Note the order by item_number will be using a two pass sort, (get the data and then sort it in the number order) so an index / key will chop that out straight off so you'd want that index even with a surrogate key.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37374

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.

Upvotes: 1

Related Questions