MySQL FOREIGN KEY vs JOIN Benchmark
Posted on 2011-03-21
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.