Are there any performance (speed) benefits to using Foreign Key?

Question

I have 2 schemas:

-- borrowed from https://stackoverflow.com/q/7745609/808921
 
CREATE TABLE IF NOT EXISTS `books` (
  `id` int(6) unsigned NOT NULL,
  `book_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
 
 
CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(6) unsigned NOT NULL,
  `tag` varchar(100) NOT NULL,
  `book_id` int(6)  unsigned NOT NULL,
  PRIMARY KEY (`tag_id`)
);
 
INSERT INTO `books` (`id`, `book_name`) VALUES
  ('1',  'The earth is flat'),
  ('2', 'One hundred angels can dance on the head of a pin'),
  ('3',  'The earth is flat and rests on a bull\'s horn'),
  ('4', 'The earth is like a ball.');
 
 
 
INSERT INTO `tags` (`tag_id`, `tag`, `book_id`) VALUES
  ('1',  'earth', '1'),
  ('2', 'flat', '1'),
  ('3',  'ball', '5'),
  ('4', 'bull', '4');

and

-- borrowed from https://stackoverflow.com/q/7745609/808921

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(6) unsigned NOT NULL,
  `book_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(6) unsigned NOT NULL,
  `tag` varchar(100) NOT NULL,
  `book_id` int(6)  unsigned NOT NULL,
  PRIMARY KEY (`tag_id`),
  FOREIGN KEY (`book_id`) REFERENCES books(`id`)
);

INSERT INTO `books` (`id`, `book_name`) VALUES
  ('1',  'The earth is flat'),
  ('2', 'One hundred angels can dance on the head of a pin'),
  ('3',  'The earth is flat and rests on a bull\'s horn'),
  ('4', 'The earth is like a ball.');



INSERT INTO `tags` (`tag_id`, `tag`, `book_id`) VALUES
  ('1',  'earth', '1'),
  ('2', 'flat', '1'),
  ('4', 'bull', '4');

and now a query:

select * from books
inner join tags as t 
on books.id=t.tag_id
where book_name LIKE '%earth%'

Is the schema with Foreign Key going to be faster than the one without? Yes, I know foreign keys are not about speed - they help in validation of the values. However, I am concerned about speed in this case because my proprietary db doesn't support foreign keys.

SQL fiddle (with fk) -> http://sqlfiddle.com/#!9/f25b99/3

SQL fiddle (without fk) -> http://sqlfiddle.com/#!9/2043b8/4

Does this answer your question? [Does Foreign Key improve query performance?](https://stackoverflow.com/questions/507179/does-foreign-key-improve-query-performance) — philipxy, Mar 09 '22 at 04:44
@philipxy - That link refers to SQL Server. A big difference is that MySQL _does_ create an index (if one does not already exist). — Rick James, Mar 09 '22 at 16:08
@RickJames The question is & could expected to be an old duplicate that should be closed & not answered. The given link has applicable generic content. I googled the title with 'site:stackoverflow.com', which is already more research than the question reflects. The asker can start with adding 'mysql'. [ask] [Help] — philipxy, Mar 10 '22 at 12:48

score 1 · Answer 1 · answered Mar 09 '22 at 02:01

Yes and No.

It is a performance benefit in that it creates an index in one of the tables. This may be a significant benefit to some SQL statements. You could get the equivalent by including an INDEX yourself.

It is a performance burden in that it will check to make sure there is a matching row whenever an INSERT is performed. Also integrity checks are performed for UPDATEs and DELETEs.

It is an integrity check as mentioned above. Depending on what you are doing, the check is likely to be redundant with your code.

Are there any performance (speed) benefits to using Foreign Key?

1 Answers1