Deriving Ideal Indexes For Your SQL Database: A Guide

Indexes are there to simplify our work when searching for data: they speed up SELECT queries at the expense of slowing down other kinds of queries like DELETEs, UPDATEs, and INSERTs instead. However, as awesome as indexes might be, they also need a lot of work to get right – in this blog, we will tell you how you should go about deriving ideal indexes for your database. The majority of the examples in this article will focus on MySQL: however, the concept is the same for all major database management systems available on the market today.

What are Indexes?

If you are familiar with database structures, great – because that’s essentially what indexes are! Indexes are database structures that can be used to quickly find rows having specific column values. At the expense of taking up disk space and time if your tables are big and you find yourself adding indexes on top of them, indexes allow databases to skip reading through entire tables and instead, only scan relevant rows which means that databases have less data to scan through.

What are Ideal Indexes?

With that out of the way, what is an “ideal” index? We could define a perfect index like so – such an index is any index that is capable of helping us pursue our data reading goals without putting a toll on time, without occupying much disk space in return and not slowing down INSERTs, UPDATEs, and DELETEs to a point where work is incomprehensible. In other words, an ideal index is any kind of index that allows us to speed up search operations, but at the same time doesn’t put a huge strain to maintain a database.

Before choosing an “ideal” index for our database instance, we must decide what kind of an index type we are going to employ. For that, it is always useful to employ a questionnaire:

• What kind of searches are we running? Do we deal with wildcards? A B-Tree index would do.
• Do we need exotic search operations (think queries involving wildcards and the like)? A FULLTEXT index will do.
• Do we work with data that needs to be unique (think about data without duplicates)? We should head over to the UNIQUE INDEX space.

Once we have chosen our index type, it’s type to derive an ideal index.

Deriving Ideal Indexes

To not bore you with theory, we are going to get straight to the point – how do you derive an ideal index? It must be different for every scenario, no?

In most cases, the approach outlined below would do:

• Pick an index that most suits your use case. In most cases, ordinary indexes would do. In others when we find ourselves searching for Boolean and other data, we employ a FULLTEXT index. For more details, refer to our indexing for high performance post.
• Ensure your queries are designed properly – ensure that your SELECT query only selects the fields that need to be returned instead of using SELECT * and employ a WHERE clause – if it’s not there, indexes are generally useless and won’t be used.

The entire situation could look something like this.

a) Suppose you have five columns and you need to index three of them.

1. You decide to use a B-Tree index and index multiple columns at the same time.
2. Put the column that you use straight after the WHERE clause into the index first.
3. If you use ORDER BY, you should add the columns that you use this statement on afterwards. Do not use the columns that you’ve used in the previous step and do not change their order: that will make your query performance a little worse.
4. If we want our query to be as fast as possible, we add all other columns that were left out after completing the third step.

The steps above describe a covering index. Covering indexes are a type of index where all of the columns required for a query to execute are included in the index and it's frequently added to a table when using ALTER TABLE queries. Here's how one ALTER TABLE query would look in such a scenario:

ALTER TABLE demo_table ADD INDEX demo_idx(demo_c1, demo_c2, demo_c3);

demo_c1 is the column that goes straight after the WHERE clause, demo_c2 is the column that goes straight after the ORDER BY statement, and demo_c3 is our “remaining” column. That’s it: we now have a covering index! Now we will tell you what your SELECT query would look like. In most cases, it would look something like this:

SELECT demo_c3 FROM demo_table WHERE demo_c1 = ‘Demo’ [ORDER BY demo_c2 ASC|DESC];

demo_c1, in this case, would be the column that the search is being run on: demo_c2 would have integer values or dates, so MySQL could sort it. You get the idea.

Designing indexes in such a way might not always be possible, however, always consider how much data you have and what kind of queries you run. If you work with bigger data sets and indexing the entire column is simply not feasible, you might also find that it might be feasible to index a prefix of the data to make your query performance a little better: such an index won't occupy a big chunk of hard drive space and it will offer you slightly better performance as well.

For many other cases, a mix of performance optimization and indexing will be required and your steps will require work with a B-Tree index. Follow these steps:

1. Get an approximate number of the rows that are being held in your table that you need to index and ensure the table is running InnoDB as the primary storage engine (MyISAM is known to be unreliable as it is prone to crashes and similar things.)
2. Make sure your InnoDB engine is optimized for high performance by modifying your my.cnf file (modify my.ini if you intend to run Windows.)
3. Only add the index on columns that you run SELECT queries on. If you intend to ALTER the table, ensure to do it after you load data into it and not before as MySQL and its flavors make a copy of the table when altering it.

Follow these steps, ensure you read on our indexing and optimizing for high performance tutorials, and you should be good to go in regards to your database performance, but if you see that your database performance is still not up to par, it might be time to glance into your database structure.

Arctype and Your Database Structure

In case you’re not aware of it already, an ideal index is not really “ideal” if your database structure is messed up. If you fail to utilize basic concepts of normalization and don’t know the difference between normalization forms, you might face some problems as well. Thankfully, you don’t need to search far and wide in this space – Arctype is the tool that can help you take care of your database structure and indexes as well. Launch Arctype and you will instantly be suggested the steps you should take:

Click on one of your tables, and you will be able to see their structure!

Once you see the structure of a given table, you will no longer be hard-pressed to decide what kind of indexes you want to employ. You will also be able to better choose the length of a given data type: you will have the columns and their length already displayed in front of you. Can it get easier than that? Arctype will also display a list of tables on the left-hand side: expand those and you will be able to better understand what other tables consist of as well.

Once you write your queries, do not forget that the Arctype client also has a capability to share them amongst your team and colleagues and even enables you to edit your table data on-the-spot. Click on any index that is added onto a table, and you will see a query that will let you recreate it. Awesome, right?

However, we will not spoil all the fun for you. Try Arctype yourself and you will see what it can do: the features we have covered here are merely starting points, the client has a lot of other features to offer too.

Summary

Deriving ideal indexes in any kind of database management system is not a very straightforward process, however, with enough knowledge about queries, database structure, and data sets in general we can make it work – we hope that this blog post has provided you with some information on that front.

Keep in mind that we publish a wide variety of topics ranging from web development to analytics and dashboards, so if this article hasn’t yet hooked you into the Arctype world, make sure to have a read through our other articles inside the Arctype blog and you will be surprised with what you can find!