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
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.
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,
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
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.
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.
