0

I'm trying to construct a database by using laravel relationships. As far as i know, foreign keys are a good way to ensure data integrity and correctness. Why not? I'd want to use it.

My questions are :

a)does using foreign keys in mysql really slow the performance at all? if yes, why does it slow?

b) If it slows, what should I do? Is it better not to use it at all ?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Chemistry
  • 165
  • 7
  • 1
    related: https://stackoverflow.com/questions/507179/does-foreign-key-improve-query-performance – dustytrash Sep 11 '18 at 18:33
  • I know that. but they mention things I need to google. I asked for a simpler answer. :) – Chemistry Sep 11 '18 at 18:37
  • You should change your question and let us know you don't care to google and want something 'simpler' :-) – dustytrash Sep 11 '18 at 18:38
  • Unless you're doing tens of thousands per second you probably won't notice the overhead. Modern versions of MySQL on modern hardware, especially SSD-backed servers, can process utterly ridiculous levels of transactions per second. Don't worry about problems like this until you have a well-defined, *measurable* problem. – tadman Sep 11 '18 at 18:39
  • So, I can use foreign keys without giving it a thought. Can mysql and foreign keys work for 100,000 transactions per second without slowing performance? – Chemistry Sep 11 '18 at 18:42

1 Answers1

0

A. Yes and no. Say you include a userID in the large table that stores all the stack overflow questions, where the user ID is for the person who posted the question. The type of query that would be affected by this is if you look up all stack overflow questions for a particular userID.
The reasoning goes something like "if there are a billion questions randomly ordered, how do you know you've found all the questions for the user Giorgi?". Intuition tells us you'd have to check all billion questions. The important pieces here are that 1. there are a huge number of records 2. you have to check each of them to know you've found all matching questions.

However it will only affect searches that use this new field.

So then to answer your second question:

B. If we sort the questions by userID, then we don't have to look at all of the records to know we've found them all! In SQL this is known as "indexing a table". You do it by specifying the table (in this case questions) and then what fields you want to index or sort by (userID).

This is a common practice in SQL optimization, especially in foreign keys that are on large tables. In my opinion anything over 10,000 records could be a candidate for indexing this way, but the real benchmark is whether or not your searches are performing slowly. If they aren't causing any issues then there's no reason to index since there is a small overhead associated with it.

Hope this answers your questions!

yo conway
  • 207
  • 1
  • 5