Performance is a one of metrics both important and difficult to achieve when design and implement in database system. There is many reasons make a database work very slow such as a large transaction is processed, poor schema design, or bad queries. The DBAs cannot happy with them. 😢
A solution can improve performance overall without hardly config and implementation is using indexes. Before learn which benefits that the indexes can have, we should take in what is indexes.
What is indexes?
Indexes is a schema object is stored to speed data access. Indexes is built from popular data structure include B-Tree, bitmap and hash table. Most databases implemented them and you can use it with one command. This is a impressive and powerful.
How the indexes can improve performance?
Obviously, all queries is processed in a component is named optimizer. By default, optimizer analysis and select a best plan to execute query. When a table has indexes, optimizer can use fast table scan instead of using full table scan. This reduce number of records is processed, so reduce time-consuming query. If you want to learn more about execution plan using indexes, you can read my articles in the next time.
Indexes best practices
Determine right indexes on right tables: Because indexes make DML statements is executed slowly, you should consider a column whether build indexes or not. You can evaluate them in test environment before build them in production.
Check indexes fragment frequently: After use in the long time, a index can be fragment and decrease performance system because delete statements. In that time, you should de-fragment or rebuilt them in low traffic transaction such as midnight, …
If this article is useful, please encourage me by clapping. Thanks for reading. 😊
