What is the cost of adding a foreign key in mysql?

Posted on

Question :

I have an application which has a product_def table defining all products.
The recrods in product_def are fixed and will never be deleted.

CREATE TABLE `product_def` (  
`product_id` INT( 5 ) UNSIGNED AUTO_INCREMENT ,
`product_name` VARCHAR( 20 ) DEFAULT NULL ,
PRIMARY KEY ( region_id ),
) AUTO_INCREMENT=1 ENGINE=innodb CHARACTER SET utf8 COLLATE utf8_general_ci   ;

I have another order_records table which contains 5 products in a record.
This order_records table will be gigantic. I am wondering whether I shall use foreign keys in order_records to refer to product_def table.
What is the cost to use foreign keys in a table in consideration of data size and searching or updating speed?
My case here is that the records in product_def table will never be deleted, therefore data integrity is not the concern here.

My questions are :

(1) Assume order_records table has records 10G in size without those 5 foreign keys. Then how big order_records will be if I add 5 foreign keys to the table?

(2) What is the performance difference in updating and selecting from database after adding 5 foreign keys?
(The table order_records will be extremely huge.)

CREATE TABLE `order_records` (  
`order_id` BIGINT( 5 ) UNSIGNED AUTO_INCREMENT ,
`order_1_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_2_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_3_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_4_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_5_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`name` VARCHAR( 20 ) DEFAULT NULL ,
PRIMARY KEY ( order_id ),
FOREIGN KEY ( order_1_product_id ) REFERENCES product_def(product_id)  ON UPDATE CASCADE
   ???  use foreign key  ????
FOREIGN KEY ( order_5_product_id ) REFERENCES product_def(product_id)  ON UPDATE CASCADE
) AUTO_INCREMENT=1 ENGINE=innodb CHARACTER SET utf8 COLLATE utf8_general_ci   ;

Answer :

In MySQL, a foreign key requires an index. If an index already exists, the foreign key will use that index (even using the prefix of an existing multi-column index). If no index exists, defining the foreign key will build the index.

So the size increase and time to create a foreign key is about the same as to create an index on the same column(s).

The performance of a SELECT is not impacted significantly by the presence of a foreign key. Only a slight additional work for the optimizer, to consider the new indexes.

The performance of updating is more, because for each foreign key, an INSERT/UPDATE/DELETE has to check to see if the constraint is satisfied. That means a primary key lookup to the referenced tables. This impact is measurable, and it is greater if the referenced tables are not in the buffer pool.

Another impact is the locking. If I update a row in a child row that has a foreign key, InnoDB places a shared-lock on the referenced rows in the parent tables. That means no one can update those parent rows until I commit. If you have lots of threads updating child rows, then the parent rows may be locked most of the time, and this can make it hard to do concurrent work in the parent tables. Not so much a performance problem, but a concurrency problem.

As with all “how does that perform” questions, the answer really depends on your workload. If you don’t have concurrent updates, for example, that issue may not effect you for all practical purposes. Testing it yourself with load testing is the only way to be sure. It’s not something anyone on StackOverflow can answer precisely for you.

Leave a Reply

Your email address will not be published. Required fields are marked *