When and How Best to Index Data?

Dive deep into ways to best index your data and learn how to mysql if index does not exist create with BreachDirectory.

Preface

Indexing data is of interest to every developer and DBA that aims to improve query performance. Ways to best index data vary according to situations you find yourself in, however, there are tried and tested paths you can take to create an index during a mysql if index does not exist create operation.

When to Build Indexes in MySQL?

Not all use cases necessitate indexing of your data. Consider mysql if index does not exist create if one or more of the following points rings true for your use case:

1. Your use case necessitates search operations and you have tables comprised of tens of millions of rows or even more.
2. You want to improve the performance of your SELECT queries and, due to the volume of data, partitioning and normalization doesn’t quite cut the chase.
3. Your database is performing more read operations than write operations and you have a lot of data you’re working with.
4. You have a use case that necessitates a specific index type to be used (e.g. you need to remove duplicate rows from a table – you would then use a UNIQUE index to drop these rows.)

There may be many reasons why you would necessitate indexing for your use case, but whatever the case, please evaluate your use case carefully and understand that an index is a data structure that allows your database to quickly find rows by filtering some of them out. Then, choose the type of index your database is most likely to help you with and create it inside of your DBMS.

How to Create Indexes in MySQL?

When creating indexes, aim to create as few SQL indexes as necessary, and check on them frequently. Indexes may become fragmented if there’s a constant flow of new data inside of your table, if your data is frequently updated, or deleted. Fragmentation means that SQL indexes that are created for your use case will have a lesser effect than before (e.g. your queries may not use them, or use them less frequently until they are rebuilt.)

When performing a mysql if index does not exist create operation, first keep in mind that there’s multiple types of indexes you can choose from. Depending on your database management system of choice, you may have richer or poorer choice menu, but in MySQL, it may look like this:

1. B-tree indexes – these indexes are the vanilla index type in MySQL. B-tree indexes are defined when no other index type is specified in the index definition.
2. Unique indexes – these types of indexes ensure that no duplicate values are inserted into a column that they reside on.
3. FULLTEXT indexes – these types of indexes allow you to search for data with “fuzzy matching” – e.g. enable you to search for search values containing a wildcard and the like.
4. Clustered or PRIMARY KEY indexes – these types of indexes are mostly used when we need to insert automatically incrementing ID values into of a table. Our table can only have one clustered index due to its design.
5. Descending indexes – these types of mysql if index does not exist create indexes store data in a descending manner which can be specified using the DESC query clause.
6. Partial indexes – these types of indexes in MySQL index only a part of the data, hence the name. For example, they would index the first 10 characters of a column and thus save disk space which is precisely what they’re used for.
7. Covering indexes – these types of indexes “cover” all of the columns required for the SQL query to successfully execute and when these kinds of indexes are in use, our database can read the data from the index instead of the disk and produce exceptional speed.
8. Spatial indexes – these kinds of SQL indexes are used to index geospatial (geographical) data and they would act on vector values.

Most use cases would necessitate a B-tree index. This type of index is called a “vanilla” index because it doesn’t necessitate any special definitions and indexes will be B-tree indexes by default if no index type is specified.

Before creating any index on a column, it may be a good idea to inspect the table in question and figure out whether any indexes exist in the first place. For that, you can either make use of phpMyAdmin or any SQL client:

Image 1 – phpMyAdmin Showing the Indexes on a Table

Or a SHOW INDEXES FROM [table_name] query as shown below – results will be identical:

Image 2 – SHOW INDEXES FROM table in MySQL

Inspect the existing indexes on your table (paying attention to the cardinality won‘t hurt either since it refers to the number of unique values in the index), if necessary, drop one or two, and if you decide that another index is necessary, perform a mysql if index does not create operation.

To perform a mysql if index does not exist create operation for a B-tree index, use this SQL query:

CREATE INDEX `idx_name` ON `demo_table` (`demo_column`);

This SQL query will create an index named idx_name on a demo_column inside of a demo_table. Adjust as necessary:

Image 3 – Performing a mysql if index does not create operation in MySQL

To add a unique index and remove all duplicate rows as a result, specify a UNIQUE clause like so:

CREATE UNIQUE INDEX `demo_idx` ON `demo_table` (`demo_column`);

Alternatively, if you run older versions of MySQL, specify an IGNORE clause to ignore all duplicate key errors like so:

ALTER IGNORE TABLE `demo_table` ADD UNIQUE INDEX `demo_idx` (`column`);

To create a covering index, “cover” all of the columns used by your query. That means that if your SQL query looks like so:

SELECT * FROM `demo_table` WHERE `a` = ‘Demo’ AND `b` = ‘Value’ AND `c` = ‘Test’;

You would benefit from a covering index like so:

CREATE INDEX `covering_idx` ON `demo_table` (`a`,`b`,c`);

MySQL >= 8.0 can also read the covering index backward.

To add a primary key, consider adding it once creating a table like so:

CREATE TABLE `demo_table` (
`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
...
);

Or add it once your table is already created like so:

ALTER TABLE `demo_table` ADD PRIMARY KEY (`col1`, `col2`, ...);

If you have a lot of rows in the column and still want an index, consider using a partial index and define an index on the first X characters in a column instead (the query below will index the first 10 characters):

CREATE INDEX `demo_idx` ON `demo_table` (`demo_column`(10));

Finally, if you find yourself working with geospatial data, add a geospatial index like so:

ALTER TABLE `geometry` ADD SPATIAL INDEX (`column`);

That’s kind of it. Evaluate your use case and choose an index carefully and always remember that an index will always slow down INSERT, UPDATE, and DELETE operations because when data is inserted, updated, or deleted, the data in the index has to be inserted, updated, or deleted as well.

Summary

Mysql if index does not exist create is not rocket science – indexes in MySQL can be created by running a couple of SQL queries that help us do that. Before creating SQL indexes on a table though, consider them only if your use case warrants such procedures (i.e. your table has enough data to necessitate an index), and choose the type of index you are going to use very carefully. An improper index type can be a highway to hell, at least figuratively speaking.

Frequently Asked Questions

What is an Index? Why Should I Perform MySQL If Index Does Not Exist Create Operation?

An index is a data structure that your database can use to quickly find rows. Such data structures consume a lot of space on the disk and slow down INSERT, UPDATE, and DELETE operations, but can drastically improve SELECT – reading – operations instead.

Why Would I Use an Index?

Consider using an index to drastically improve your read operations if you have a lot of data to sift through.

How to Perform a MySQL If Index Does Not Exist Create Operation in MySQL?

To perform a mysql if index does not exist create operation, first decide what kind of index you are going to use. Then, define the index on top of the necessary column(s) as shown in this blog.