Database indexes : advantages and disadvantages


If you’re software engineering, surely, you will not be strange with database indexes.

But, when working in project, the knowledge about database index is very import. If you don’t know it, sometimes you will be too abusive database indexes. That’s not good for query, sometime they make query execute time longer.

With me, with a four-year experience, I like to ask new fresher a question about database indexes. But no many people can answer. That’s why I write this post, share my understand about database indexes, when we used and how many is enough?

In most common case, database indexes very helpful, that make application run faster. But that’s not always true. You should know the disadvantages when use indexes.

Before we begin, to understand the advantages about indexes. First question is “What’s database indexes?”

1. What is database indexes?

From Wikipedia:

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

As you can see, there are more nodes (twice more). Indeed, you have additional nodes, the “decision nodes” that will help you to find the right node (that stores the location of the rows in the associated table)

2. Advantages

2.1 Increased performance

Everybody should know, using database indexes will help your query faster. There is no need to access a row in the database from an index structure, so you can reduce the total number of I/O operations needed to retrieve data.

In a most common usually, the database indexes will help speed up SELECT queries and reports.

2.2 Using like primary key

Indexes like primary key index and unique index help to avoid duplicate row data. That’s very useful, with database indexes, you can use it like primary key.

Furthermore, indexes will make faster access (when the index is being used), and the ability to enforce certain business logic like no duplicates.

3. Disadvantages

3.1 Memory

First, of course, that is memory. One index in a table is not a big deal, but if we have too many indexes?. The index itself occupies space on disk and memory (when used). So, if space or memory are issues then too many indexes could be a problem. In my current company, I saw that guy add 15 indexes (for 15 columns) on a table. That’s so funny, because that not make your query faster.

3.2 Time when insert/update/detele

Yeah, when we have many database indexes (in the table with many constraints). It’s terrible to make insert/update or delete statement. Before excute that action, we need to check constraints and indexes. The index needs to be maintained as well as the original data. This slows down updates and locks the tables (or parts of the tables), which can affect query processing.

In reality, if you index every column in every table, then data modifications would slow down. If your data is static, then this is not an issue. However, eating up all the memory with indexes could be an issue.

with more database indexes, slower time to DELETE
More indexes, slower DELETE

4. When we should use database indexes

That’s difficult to say what’s exactly situtation we should use indexes.

But, each index is a system-managed table, so every addition or modification to the data in a user table potentially involves updating the indexes, which can cause slower performance of data updates. Everyone—from product vendors to database-performance specialists to DBAs who work on the production front lines—has ideas about how and when to index.

With table, we should indexes with table not has many insert/update/delete action. In case we want to improve query, we should use indexes for query, not for table.


5. Reference

Có gì thắc mắc cứ comment đây nha! - Please feel free to comment here!
Chia sẻ bài viết

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *