Solved

MySQL FOREIGN KEY vs JOIN Benchmark

Posted on 2011-03-21
5
832 Views
Last Modified: 2012-06-22
Question, in a nutshell: At what point do FKs start/stop outperforming JOIN statements? Yes they are two different functions, but they give the same end result for the situation below.

MySQL 5.0.51, PHP 5.2.6, Apache 2.2

---

I had a thought last night before bed and I haven't had a chance to set something up yet to test this, so I thought I'd ask here and see if anyone has done, or read something similar in the past.

I have several tables in a database all set up lovely. One is customers (id, name, postcode), one is supplier (id, name, telephone) and one is stock (id, name customer, supplier). This has simplified it greatly -- each table actually has somewhere around 20 fields for various bits of information.

Stock has around 8,000 entries; Customers has around 9,000 entries and Supplier has around 40 entries. stock.supplier is linked by FK to supplier.name; stock.customer is linked by FK to customers.name;

Customer names very rarely change (maybe one to five times per year) and supplier names never change -- because of this I think FK is the best route; However, are FKs in this instance going to outperform JOINing SQL statements? In my head it makes less overhead selecting from two / three tables for each stock item called (probably 1,000/day) with the added benefit of easier, simpler queries.

The downside is larger database tables (rather than storing customers.id and supplier.id in the stock.x fields, it has the name).

I know for tables of this size, and usage this low it probably isn't going to matter much.

Hope this makes sense. If no answers before I get a chance to set up a sim, I'll post my results.
0
Comment
Question by:Vampireofdarkness
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35178611
you are right: with tables that small, it won't really matter.
still, better use the good design you posted above, it will safe you from other issues later.
0
 
LVL 3

Expert Comment

by:greisch
ID: 35178695
It's bad design to use something that can change as a key (even a foreign key).
So you should replace the customer name by costomer id and supplier name by supplier id in the stock table and join the tables to find names back.
0
 
LVL 9

Author Comment

by:Vampireofdarkness
ID: 35178712
The non-simplified version would check by ID, not by name
The downside is larger database tables (rather than storing customers.id and supplier.id in the stock.x fields, it has the name).

I perhaps didn't make it clear enough, but if using FK it will have both id and name. ID for referencing back if more information is needed, name for not having to use a join to get the name where required. All functionality at the moment is based on IDs that never change.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35181110
I would not use Foreign Keys. I have always found that JOINs work very well as long as every column in the JOJN's ON clause is indexed as well as every column in a WHERE clause. That is the show-stopper, fail to index the correct columns and you might as well just not bother running the query.
0
 
LVL 3

Expert Comment

by:greisch
ID: 35187149
It's always a bad idea to duplicate information.
It's always a bad idea to use field that can change to link tables, with or without foreign key. Because if you change the value in one table you have to change it in all the other tables where it's used.

FK express a constraint between tables. JOIN is used to retreave data.

You don't need to use FK. But, as bportlock said, the fields used to join tables should be indexed (performance).
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question