Default Database Primary, Foreign, and Unique Key Indexing
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see what is the default database Primary, Foreign, and Unique Key indexing strategy when using Oracle, SQL Server, PostgreSQL, and MySQL.
So, if you are wondering whether one of those top relational databases creates a default index whenever you are adding a Primary Key, Foreign Key, or Unique Key constraint, then this article is going to provide you with the answer you’ve been waiting for.
Database tables
Let’s assume we have the following post and post_comment tables that form a one-to-many table relationship:
The parent post table has two keys:
- a Primary Key constraint on the
idcolumn - a Unique Key constraint on the
slugcolumn
And the child post_comment table also has two keys:
- a Primary Key constraint on the
idcolumn - a Foreign Key constraint on the
post_idcolumn
Next, we are going to see what default indexing strategy each of the top four relational database systems chooses.
Default database key indexing with Oracle
On Oracle, the post and the post_comment tables are created as follows:
CREATE TABLE post (
id number(19,0) NOT NULL,
slug varchar2(255 char),
title varchar2(255 char),
PRIMARY KEY (id)
)
CREATE TABLE post_comment (
id number(19,0) NOT NULL,
review varchar2(255 char),
post_id number(19,0),
PRIMARY KEY (id)
)
ALTER TABLE post
ADD CONSTRAINT UK_POST_SLUG
UNIQUE (slug)
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post
To check what database indexes have been created for these two tables, we can use the following SQL query:
SELECT
ind.table_name AS table_name,
ind.index_name AS index_name,
ind_col.column_name AS column_name,
CASE
WHEN ind.uniqueness = 'UNIQUE' THEN 1
WHEN ind.uniqueness = 'NONUNIQUE' THEN 0
END AS is_unique
FROM
sys.all_indexes ind
INNER JOIN
sys.all_ind_columns ind_col ON
ind.owner = ind_col.index_owner AND
ind.index_name = ind_col.index_name
WHERE
lower(ind.table_name) IN ('post', 'post_comment')
When running the above query, we are going to get the following index information:
| TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_UNIQUE | |--------------|--------------|-------------|-----------| | POST | SYS_C0047948 | ID | 1 | | POST | UK_POST_SLUG | SLUG | 1 | | POST_COMMENT | SYS_C0047950 | ID | 1 |
Oracle creates default indexes for Primary Key and Unique Key columns.
No index is created automatically for a Foreign Key column when using Oracle.
Default database key indexing with SQL Server
When using SQL Server, the post and the post_comment tables are created using these DDL statements:
CREATE TABLE post (
id bigint NOT NULL,
slug varchar(255),
title varchar(255),
PRIMARY KEY (id)
)
CREATE TABLE post_comment (
id bigint not null,
review varchar(255),
post_id bigint,
PRIMARY KEY (id)
)
ALTER TABLE post
ADD CONSTRAINT UK_POST_SLUG
UNIQUE (slug)
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post
When using SQL Server, you can use this SQL query to check what database indexes are associated with some particular tables:
SELECT
t.name AS table_name,
ind.name AS index_name,
col.name AS column_name,
ind.is_unique AS is_unique
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON
ind.object_id = ic.object_id AND
ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON
ic.object_id = col.object_id AND
ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
t.name IN ('post', 'post_comment')
When running the above query, we are going to get the following index information:
| table_name | index_name | column_name | is_unique | |--------------|--------------------------------|-------------|-----------| | post | PK__post__3213E83F82A8BE22 | id | true | | post | UK_POST_SLUG | slug | true | | post_comment | PK__post_com__3213E83F23045CBD | id | true |
SQL Server creates default indexes for Primary Key and Unique Key columns.
No index is created automatically for a Foreign Key column when using SQL Server.
Default database key indexing with PostgreSQL
On PostgreSQL, the post and the post_comment tables are created like this:
CREATE TABLE post (
id int8 NOT NULL,
slug varchar(255),
title varchar(255), PRIMARY KEY (id)
)
CREATE TABLE post_comment (
id int8 NOT NULL,
review varchar(255),
post_id int8,
PRIMARY KEY (id)
)
ALTER TABLE IF EXISTS post
ADD CONSTRAINT UK_POST_SLUG
UNIQUE (slug)
ALTER TABLE IF EXISTS post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post
To verify the database indexes that were created by PostgreSQL for these two tables, we can use the following SQL query:
SELECT
c.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
ix.indisunique AS is_unique
FROM
pg_class c
INNER JOIN
pg_index ix ON c.oid = ix.indrelid
INNER JOIN
pg_class i ON ix.indexrelid = i.oid
INNER JOIN
pg_attribute a ON
a.attrelid = c.oid AND
a.attnum = ANY(ix.indkey)
WHERE
c.relname IN ('post', 'post_comment')
ORDER BY
c.relname
When executing the above query, we are going to get the following index result set:
| table_name | index_name | column_name | is_unique | |--------------|-------------------|-------------|-----------| | post | post_pkey | id | true | | post | uk_post_slug | slug | true | | post_comment | post_comment_pkey | id | true |
PostgreSQL creates default indexes for Primary Key and Unique Key columns.
No index is created automatically for a Foreign Key column when using PostgreSQL.
Default database key indexing with MySQL
When using MySQL, the post and the post_comment tables are created using these DDL statements:
CREATE TABLE post (
id bigint NOT NULL,
slug varchar(255),
title varchar(255),
PRIMARY KEY (id)
)
CREATE TABLE post_comment (
id bigint NOT NULL,
review varchar(255),
post_id bigint,
PRIMARY KEY (id)
)
ALTER TABLE post ADD CONSTRAINT
UK_POST_SLUG
UNIQUE (slug)
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post (id)
To check the database indexes created by default by MySQL, we can use this SQL query:
SELECT
TABLE_NAME as table_name,
INDEX_NAME AS index_name,
COLUMN_NAME as column_name,
!NON_UNIQUE AS is_unique
FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
TABLE_NAME IN ('post', 'post_comment')
When executing the above query, we are going to get the following index result set:
| table_name | index_name | column_name | is_unique | |--------------|-------------------------|-------------|-----------| | post | PRIMARY | id | 1 | | post | UK_POST_SLUG | slug | 1 | | post_comment | PRIMARY | id | 1 | | post_comment | FK_POST_COMMENT_POST_ID | post_id | 0 |
MySQL creates default indexes for all Primary Key, Unique Key, and Foreign Key columns.
I'm running an online workshop on the 11th of October about High-Performance SQL.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
In this article, we learned that Oracle, SQL Server, and PostgreSQL create database indexes for Primary Key and Unique Key columns, but not for Foreign Key columns.
So, if you have two large tables that have to be joined via a Foreign Key, then it would be more efficient if you created an index on the Foreign Key column. Otherwise, a table scan would be used instead. The SQL execution plan will tell you what strategy was used by the database engine, hence you can determine which Foreign Key columns need indexing.
For small tables that are joined via a Foreign Key, adding an index on the Foreign Key column might not be needed since the database Cost-Based Optimizer might do a table scan anyway.
MySQL creates default indexes for all Primary Key, Unique Key, and Foreign Key columns.
