MYSQL performance using relationships vs not assigning relationships
mysql
Solution
Foreign keys don't gain anything for performance that you can't do by creating an ordinary index on the "FK" column. That is, you don't necessarily need the constraint for the sake of performance.
Foreign key constraints are meant to prevent data anomalies.
Re your comment:
Right, an index is created implicitly for columns that are given PRIMARY KEY, UNIQUE KEY, or FOREIGN KEY constraints.
Whether an index would give benefit or not has nothing to do with the tables or even if they have columns that reference other tables. It has to do with the queries you run.
For example:
CREATE TABLE Departments (ID INT PRIMARY KEY, name VARCHAR(10));
CREATE TABLE Employees (ID INT PRIMARY KEY, name VARCHAR(10), DeptID INT NOT NULL);
Clearly these tables are related, but we have no index or foreign key constraint defined.
An index on Employees.DeptID would benefit this query to return all members of the training department. The query looks up a department by name first, then uses it to look up the corresponding employees, using the index.
SELECT ... FROM Departments d JOIN Employees e ON d.ID = e.DeptID
WHERE d.name = 'training';
But that index does nothing for this query that returns the department name for a specific employee. The query looks up the employee by name first, then uses it to look up the corresponding dept by primary key id.
SELECT ... FROM Departments d JOIN Employees e ON d.ID = e.DeptID
WHERE e.name = 'Bill';
Problem
I have a rather larger database, 100+ tables. The design of the database is a traditional relational database. I have primary keys in tables that associate to foreign keys. However, I have not set up any relationships in the actual mysql dba. I use PHP to access the data for a website, all the queries will join tables by join statements that have the ON set to a primary key = foreign key. The knowledge of the relationship is really all in my head and the fact that i use a very simple naming convention. All primary keys are the tablename with a PK at the end, ie tablenamePK, and all foreign keys are tablenamde with an ID at the end, ie tablenameID. Very simple to see what is a primary key and what is a foreign key when creating SQL's for PHP to run. That said, I've been debating if I should actually create these relationships in my MySQL DBA or not. Would doing so enhance performance? Performance wasn't much of an issue until recently and now I'm looking to make as many efficiencies as possible before looking into upgrading hardware.