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
id
column - a Unique Key constraint on the
slug
column
And the child post_comment
table also has two keys:
- a Primary Key constraint on the
id
column - a Foreign Key constraint on the
post_id
column
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.
