First, there is no automatic indexing of FOREIGN KEY in RDBMS except MySQL, which is a stupid behavior.
Second, in some cases indexing a FK create an included redundant index, especially when :
- the table is an associating table
- the table cames from an inherited design
Third, indexing systematically FK is not a good pratice, except if all your queries is just a join without having any other column used in any other part of the query for the children table.
Some explanations...
POINT 2
First example, associating table :
CREATE TABLE Orders (ord_id int PRIMARY KEY, ...)
CREATE TABLE Product (prd_id int PRIMARY KEY, ...)
CREATE TABLE order_details (ord_id int NOT NULL REFERENCES Orders (ord_id), prd_id int NOT NULL REFERENCES Product (prd_id), PRIMARY KEY (ord_id, prd_id), ...)
Adding an index on ord_id for order_details table is stupid, because the PRIMARY KEY has already an index (ord_id, prd_id) that can be use instead of the simple (ord_id) FK. index.
Second example, inherited table :
CREATE TABLE vehicles (vhc_id int PRIMARY KEY, ...)
CREATE TABLE vehicle_cars (vhc_id int PRIMARY KEY REFERENCES vehicles (vhc_id , ...)
Adding an index on vhc_id is even more stupid, because the PRIMARY KEY has already an index (vh_idc) that is strictly the same.
POINT 3
CREATE TABLE customers (ctm_id int PRIMARY KEY, ...)
CREATE TABLE Orders (ord_id int PRIMARY KEY, ctm_id int NOT NULL REFERENCES customers (ctm_id), ...)
Adding an index on ctm_id column in the orders table will be used only in two cases :
- the index itself is sufficient to retrieve all the data for the join
- there is few rows returned from the orders table
Subpoint 2.1 example :
SELECT ctm_name, COUNT(*)
FROM customers AS c
JOIN Orders AS o
ON c.ctm_id = o.ctm_id
GROUP BY ctm_name;
Subpoint 2.2 example :
SELECT *
FROM customers AS c
JOIN Orders AS o
ON c.ctm_id = o.ctm_id
WHERE ctm_id = 123;
In any other query, there is great "luck" that the FK index won't be used because the cost of using the index in a "seek" way, then joining the index to the table to retrieve all other columns that are not in the index, will be much more than scanning the table.
SO, SYTEMATICALLY INDEXING FOREIGN KEY IS MOSTLY STUPID !
Remember that indexes is very costly in terms of transactions when the data is modified (INSERTs, UPDATEs, DELETEs, MERGEs, TRUNCATEs...). So, do not create useless indexes !
To be clear, the best choice for every index is to have a COVERING index, which means that the one and only index itself is sufficient for the whole query, including WHERE, ON (from JOINs), HAVING, GROUP BY, ORDER BY and SELECT. This being greatly facilitated by the introduction of the INCLUDE clause for indexes in some RDBMSs (SQL Server since version 2008 and PostGreSQL since version 11 in 2018)
Of course, the choice of indexing must result from the exploitation of the database and not from a dogma!
Some RDBMS (Microsoft SQL Server since the 2008 version) systematically offers a full diagnostic of indexes to be create, and it is very rare that indexes concerning only the foreign key column either reported by the diagnostic system...
As an example, this query for Microsoft SQL Server :
SELECT * FROM sys.dm_db_missing_index_details;
Give the list of all indexes needed to help performances for queries really executed since the start of the SQL Server instance and some more details, like the potential gain, if the index is created...
