intro
For ages, indexes have been the cornerstone of database performance. Indexes are one of the main measures to speed up reading – SELECT – operations and in this blog, we‘re telling you all about them. Have a read!
Ever since open source databases have been a thing, indexes have been that something that improves their performance. In different database management systems, different indexes have different usage types – some work together with certain types of indexes, some cover queries only partly, but all of them one thing in common – they speed up SELECT operations.
Types of Database Indexes
As you may know, database indexes have a couple of types and each of them accomplish different purposes. These are the types of indexes available in MySQL alone (for the full list of possible index types, refer to the documentation):
Indexes speed up reading (SELECT) operations, but slow down all of the other operations (INSERT, DELETE, and UPDATE queries) in return.
When and How to Index?
The most frequent question regarding indexes is the time that’s best to index data. And the answer to this question is pretty straightforward, really – you should index data as soon as you feel your SELECT queries grinding to a halt or as soon as you have a lot more data than usual (100,000 rows and above is a good starting point.)
To index or not to index also heavily depends on your disk – indexes take up space, so if you’re indexing millions of rows, expect the indexing process to take a toll on the disk space and to take longer than usual.
Take a look at this table:
In this case, around 200,000 rows would be the perfect time to think about indexing – the data has space to grow, but at the same time the row count isn’t insignificant.
In most cases to add an index, you will make use of the CREATE INDEX or the ALTER TABLE query – they aren’t symlinks, but whether you run a query like CREATE INDEX idx_name(column_name) or ALTER TABLE demo_table ADD INDEX idx_name(column_name); shouldn’t make much of a difference, just bear in mind that ALTER TABLE queries work a little differently than ADD INDEX queries do: if your table is bigger, they make a copy of the table on the disk, copy all of the data to it, make the requested changes, then swap the original table with the table that the DBMS created – that will take up time as well.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
Anyway, add indexes with the ALTER or CREATE INDEX queries – if you add them using DbVisualizer, it will also tell you the time that the queries take and the status of them (if the status is FAILED, go back and check on what went wrong, then run the query again):
What Type of Index to Choose?
After you’ve decided what and when you will index, it’s time to decide what type of an index you should choose for your specific use case. In many cases, you will most likely be using B-Tree indexes: they are the most frequently used type of an index.
In other cases, though, your decision may be influenced by:
Different types of indexes are defined in different ways – some of them can be seen below, but for most of us, ordinary – B-Tree – indexes will do the trick.
Columns can also have more than one type of index on them at once to satisfy multiple types of queries at the same time.
DbVisualizer and Indexes
Once your indexes have been added, go back into the overview of your table (click on your database management system, then expand the database in question, then head over to the Tables.) Finally, click on the Indexes tab below the table itself to get ahold of all of the details:
Not only will DbVisualizer tell you what table holds what kind of an index, it will tell you many more details related to the index itself including:
Basically, you will be able to observe everything that’s necessary about the index itself – most of the details presented in this tab will be necessary for those who know a little bit about databases themselves or those who are solving issues relating to query performance when indexes are in use.
Query Performance and Indexes
As we’ve already noted, indexes are only necessary to improve the performance of reading data – they improve the performance of SELECT queries. How many times the query performance will be improved, though, is directly dependant on your queries themselves – to maximize the performance of your queries when they’re using indexes, aim to make your queries as simple as possible, that is:
Finally, bear in mind that not all queries will use indexes you’ve defined in the first place – make use of the EXPLAIN keyword in database management systems to guide you to the light: as a rule of thumb, define the column that is indexed straight after the WHERE part of the query to ensure that your query is actually using the index and you should be good to go.
Also bear in mind that indexes will slow down INSERTs, UPDATEs, and DELETEs due to the fact that data needs to be updated together with the index when it’s in use: if you’re using an index, you should allocate more time for such operations as well.
Finally, keep in mind that database maintenance has an impact on your query performance as well – we recommend using SQL clients such as DbVisualizer to gain a deeper insight into your queries and your database instance itself: your database will thank you after a while.
Summary
In this blog, we’ve walked you through the importance of using indexes in database management systems. Most of the examples provided were centered around MySQL and its counterparts such as Percona Server and MariaDB, but the concept remains the same – indexes speed up SELECT operations at the same time slowing down UPDATEs, DELETEs, and INSERTs.
Indexes have multiple types each being able to solve a different problem, so before using them, dig into these as well, do know the risks involved, and you should be good to go.
When improving query performance with indexes, keep in mind that SQL clients like DbVisualizer can also help you solve problems related to maintenance and upkeep of your databases – grab a free trial and try it today, come back to the blog to learn more about databases in a while, and until next time.
