intro

Index creation in any database management system is not a very easy task—there are lots of things you need to consider before even deciding to index the columns in your database management system, no matter what kind of DBMS you find yourself using.

Tools used in the tutorial

DBVISUALIZER

TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

DOWNLOAD

Read on and learn how to create and maintain your indexes!

What Are Indexes?

If you’re a seasoned DBA, you already know the way around your database management system of choice, be it MySQL Server or its counterparts like the MariaDB Server or Percona Server, an object-relational database management system like PostgreSQL, a database management system based on timescale data or a non-relational database management system like MongoDB.

However, there’s a topic that frequently eludes even the most experienced database administrators — that topic is related to indexes and index creation in the DBMS of your choice.

To begin with, we’ll walk you through what indexes are in the first place, and they’re not that hard to understand, really — indexes essentially are just data structures that make quick searching for specific values in data as easy as eating a piece of cake. Not without a downside — slowing down of INSERT, UPDATE, and DELETE operations is the price you will have to pay.

For some, the price may be high. That’s true, but considering what you will be gaining — possible hundred-fold or even thousand-fold increases in SELECT query performance — it may be the price that’s worth investing in.

Types of Indexes

Indexes in any database management system don’t come alone — there are many types of indexes available for you to use. Different database management systems offer different types of SQL indexes, and we list some SQL index types for you below:

B-Tree indexes — B-Tree indexes are the “normal,” “default” indexes in your database management system. They’re the indexes that get introduced onto your column once you run a CREATE INDEX SQL query.
Unique indexes — unique indexes get rid of duplicate rows in a column they’re defined on.
Spatial indexes — geospatial, or “spatial,” SQL indexes are used for columns of the geometry type that are defined as NOT NULL.
Clustered indexes — clustered indexes are indexes in which the order of the rows in the data pages is ordered in the exact same way as the order of the rows in the index.
Hash indexes — hash indexes are a specific kind of index that helps direct lookups, but they work only in memory (i.e., the MEMORY storage engine in MySQL.)
Covering indexes — covering indexes “cover” all columns required for a specific SQL query to successfully execute; queries that fully use the covering index read the index instead of reading the data itself, thus making our SQL query significantly faster.

Index Creation in Your DBMS

With that being said, keep in mind that index creation in SQL isn’t rocket science. The vast majority of database management systems offer two ways to create indexes. These are SQL queries that look like so:

CREATE INDEX idx_name(column_name);
ALTER TABLE demo_table ADD INDEX idx_name(column_name);

These two methods are similar, but different in their regard — both queries will build (create) an index called idx_name on a column called column_name, but a CREATE INDEX query (the first example) will only create the index if a name (idx_name) is specified. Otherwise, the functionality of these two queries is identical.

With that being said, one can also perform index creation after building a table like so:

Creating Indexes Upon Table Creation in DbVisualizer

In the aforementioned example, you see that there are a couple of different types of indexes that are being created—we create:

A PRIMARY KEY — an index called a primary key helps avoid duplicates in a column and increments the column value by 1 whenever a new row is added to the table.
A UNIQUE index disallows duplicate values. Duplicate values being inserted will error unless the IGNORE statement is specified.
3 B-Tree indexes. As you can see, we have three of them.

You will also see that our indexes have different names — for example, the SQL index on the username column has a name of uname instead of username. All good — not all indexes are named exactly after the columns they provide for.

Things to Consider

Now that you know how to create indexes in your database management system (the aforementioned examples are based on MySQL Server, but the process is pretty much the same for all database management systems), there are a couple of things you need to keep in mind:

All ALTER TABLE SQL queries will make a copy of the table on the disk, then perform operations on that table, and swap the tables once the operation is complete. This is usually done in the background and without the developer or DBA noticing. Usually, this isn’t an issue unless you run bigger data sets (i.e., data sets with >100M or more rows.)
If you use the CREATE INDEX SQL query to build your SQL indexes, you must specify a name for your index. As we’ve already mentioned before, the name doesn’t need to be the same as the column the index provides for, though it’s recommended to do so in index creation unless you have good reasons to not name your indexes after your columns.
Indexes will typically slow down maintenance operations — here we’re talking about CREATE, INSERT, and UPDATE queries. That’s the case because whenever indexes are in place, data that’s created, inserted, or updated needs to tell your DBMS to update the values in the index too. On the other side of the coin, indexes will make your SELECT queries blazing fast. If the performance isn’t enough, however, you may want to consider using partitions too.
If you have a lot of data, consider adding indexes after you insert data — that should make your maintenance operations a little faster because of things mentioned in point #3.
Inspect your indexes periodically — there is such a thing called index cardinality — index cardinality essentially refers to the values stored inside a specific index. The bigger this value is, the better for your database.
Use EXPLAIN or similar SQL queries when possible — SQL queries like EXPLAIN can walk you through what SQL indexes are used within your database management system — more importantly, they will tell you how the indexes are being used as well.
Be aware that SQL index creation is not the same in all database management systems and that some types of indexes will need to be created differently, too — covering indexes, for example, will need to have the length of the column specified within themselves as well.

SQL Clients

Finally, after you’ve walked yourself through the aforementioned points and are sure that you’re implementing indexes correctly, you might also want to turn your head towards SQL clients such as the one provided by DbVisualizer.

SQL clients like DbVisualizer are a great choice for DBAs and other software professionals because they are capable of solving many database-related problems and issues with their SQL clients, ERD-like schema generation abilities, top-notch security features, an auto-completer, visual query builder, and so much more. There’s a reason why companies like NASA, Tesla, and Volkswagen consider DbVisualizer to be their #1 choice as far as SQL clients are considered but if you’re still not convinced, feel free to grab a free 21-day trial and figure everything out for yourself.

Summary

Index creation in database management systems may seem like a piece of cake, but for many DBAs, it isn’t. That’s the case because SQL indexes have many unique characteristics unique to themselves and small things mean a lot for your database—from the type of index you choose to the SQL client you find yourself using. Everything counts.

We hope that you’ve found this blog of ours informative and useful, come back to our blog for more interesting content in the database space in the future, and until next time.

Those of you who are searching for database tutorials in video format may find the Database Dive YouTube channel useful, too—that channel covers everything from performance to availability, so if you’ve read so far, definitely give videos a watch too.

FAQ

What is index creation?

In the database world, index creation refers to the process of creating indexes — or keys — that make the performance of read-based SQL queries (SELECT queries) faster.

Why are there so many types of indexes?

The reason there are so many types of SQL indexes available to choose from is that each type of index serves a specific purpose—some indexes are used to remove duplicate values, others are used to automatically increment values in columns, etc. For example, a PostgreSQL GIN index is great for full-text search.

Where can I learn more about Indexes and Database Performance?

You can learn more about the open-source database world on the Database Dive YouTube channel.

Follow our blog for updates too!

Why should I use an SQL client?

DbVisualizer is a feature-rich SQL client rated as the #1 choice for software companies worldwide. Give it a try today — you won’t be disappointed.

Index Creation in SQL

What Are Indexes?

Types of Indexes

Index Creation in Your DBMS

Things to Consider

SQL Clients

Summary

FAQ

What is index creation?

Why are there so many types of indexes?

Where can I learn more about Indexes and Database Performance?

Why should I use an SQL client?

A Guide To the SQL DECLARE Statement

SQL Upsert: Inserting a Record If It Does Not Exist

A Complete Guide to the MySQL COUNT Function

How to Find Elements In an Array: PostgreSQL Guide

MySQL Nullable Columns: Everything You Need to Know

A Complete Guide to the MySQL GROUP_CONCAT Function

How and Why to Add an Index in SQL

MySQL 9.0 and the New EXPLAIN Features

MySQL CAST: A Guide to the Data Conversion Function

Postgres Create Array of Numbers: Complete Guide

Product

Partners

Legal

Company

Social