Indexing Foreign Key Columns
Fast recall
Quick revision
Core answer
Understanding when a foreign-key index helps and how InnoDB and PostgreSQL differ on referencing-side indexes
Applying a foreign-key index rule without checking the database, workload, and query plan
An index gives the optimizer another access path but is not guaranteed to be chosen; compare InnoDB's required suitable index with PostgreSQL's manual referencing-side decision
Interview-ready guide
Interview answer
Foreign-key columns are often worth indexing, but the right answer depends on the workload.
An index can help joins, child lookups, and the work needed when a parent row is updated or deleted. Without a suitable index, the database may scan the child table; with one, it has a faster access path, although the optimizer can still choose a scan when that is cheaper.
Database behavior differs: InnoDB requires a suitable index for a foreign key and can create one, while PostgreSQL does not automatically index the referencing columns. Each index also consumes space and slows writes, so define it deliberately in a migration and inspect representative queries with EXPLAIN or EXPLAIN ANALYZE.
For queries such as orders by customer and date, a composite (customer_id, order_date) index may be more useful than a foreign-key-only index.
Indexing Foreign Key Columns
Direct Answer
Foreign-key columns are often good index candidates because joins, child lookups, and parent updates or deletes may search them. The index is not free: it takes space and slows writes, so confirm it from the workload and query plan. InnoDB requires an index for the constraint and can create one; PostgreSQL does not automatically index the referencing columns.
Code Example
<!-- Liquibase migration for creating an indexed foreign key -->
<changeSet id="2" author="dev">
<createTable tableName="orders">
<column name="id" type="bigint" autoIncrement="true">
<constraints primaryKey="true"/>
</column>
<column name="customer_id" type="bigint">
<constraints nullable="false"/>
</column>
<column name="order_date" type="timestamp"/>
</createTable>
<!-- Add foreign key constraint -->
<addForeignKeyConstraint
baseTableName="orders" baseColumnNames="customer_id"
referencedTableName="customers" referencedColumnNames="id"
constraintName="fk_orders_customer"/>
<!-- PostgreSQL requires manual index on foreign key -->
<createIndex tableName="orders" indexName="idx_orders_customer_id">
<column name="customer_id"/>
</createIndex>
</changeSet>
Common Mistakes & Pitfalls
- Assuming every database creates the same index: InnoDB requires a suitable referencing index; PostgreSQL does not create one automatically.
- Adding an index without a query: an unused index still consumes storage and write work.
- Reading EXPLAIN as a fixed promise: plans can change with statistics, parameters, data distribution, and database versions.
- Ignoring column order: choose a composite index from the predicates, ordering, and range conditions used by real queries.
Check your understanding
Answer these without looking back at the article.
- Does MySQL auto-index foreign key columns?
- Does PostgreSQL auto-index foreign key columns?
- What is the performance impact of joins on non-indexed FK columns?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.