Effective Strategies to Optimize Database Performance: Insights into Unusable Indexes

6-minute read
when to use indexes to optimize performance • mysql • database • btrees • index • data structures • database • indexing • sidsblog
when to use indexes to optimize performance • btree • b tree • bplus-trees • trees • tree • tree data structure • data structures • database • indexing • sidsblog

What do I even mean by useless indexes?

To improve the application and the database, indexes need to change over time.

As the structure of the database change, the structure of the tables change, data in the tables change, the application changes, also the queries against the data change.

Indexes that once helped performance now just bloat your database and cause extra work for inserts, updates, and deletes.

wait…

Before getting to the conclusion

What is Indexing? firstly…

It is a way to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed.

It is a data structure technique that is used to quickly locate and access the data in a database.

Indexing

–

Types of Indexing:

Indexing in Database is defined based on its indexing attributes. Two main types of indexing methods are:

Indexing Types

  • Primary Indexing
  • Secondary Indexing

–

Primary Index:

Primary Index is an ordered file which is fixed length size with two fields. The first field is the same a primary key and second, filed is pointed to that specific data block. In the primary Index, there is always one to one relationship between the entries in the index table.

The primary Indexing in DBMS is also further divided into two types.

-   Dense Index
-   Sparse Index
  • Dense Index: In a dense index, a record is created for every search key valued in the database. This helps you to search faster but needs more space to store index records. In this Indexing, method records contain search key value and points to the real record on the disk.

Dense Index

  • Sparse Index:

It is an index record that appears for only some of the values in the file. Sparse Index helps you to resolve the issues of dense Indexing in DBMS. In this method of indexing technique, a range of index columns stores the same data block address, and when data needs to be retrieved, the block address will be fetched.

However, sparse Index stores index records for only some search-key values. It needs less space, less maintenance overhead for insertion, and deletions but It is slower compared to the dense Index for locating records.

Below is an database index Example of Sparse Index

Sparse Index

–

Secondary Index

The secondary Index in DBMS can be generated by a field which has a unique value for each record, and it should be a candidate key. It is also known as a non-clustering index. This two-level database indexing technique is used to reduce the mapping size of the first level. For the first level, a large range of numbers is selected because of this; the mapping size always remains small.

–

Example of secondary Indexing

Let’s understand secondary indexing with a database index example:

In a bank account database, data is stored sequentially by acc_no; you may want to find all accounts in of a specific branch of ABC bank.

Here, you can have a secondary index in DBMS for every search-key. Index record is a record point to a bucket that contains pointers to all the records with their specific search-key value.

Secondary Index

More In-Depth Details: https://prepinsta.com/dbms/indexing-and-its-types

There are two types of database indexing architecture:

  1. Clustered
  2. Non-clustered

Both clustered and non-clustered indexes are stored and searched as B-trees, a data structure similar to a binary tree.

More Details on that: https://www.guru99.com/clustered-vs-non-clustered-index.html

If you haven’t read more on B-Trees https://sidmulajkar.com/posts/b-bplus-trees/

–

Power of Indexing:

Indexing plays a crucial role especially in applications such as eCommerce websites. Imagine that you are looking to buy a new bag on an eCommerce website.

–

You type in the search query and you expect to see a range of options that you’d like to choose from.

However, due to the absence of indexing you are stuck looking at a blank screen for the next 15 mins waiting for the relevant data to load, or even worse, you get everything ranging from garments to electronics as an answer to your result.

–

Given the number of alternative eCommerce websites on the internet, I highly doubt that you’d be willing to patiently wait around instead of simply logging onto a faster one.

When indexes need change, it requires doing a test on

How removing an index will affect the overall performance?

We have two options – either we can disable or drop the index.

Which is better?

But…

When to use Indexes:

Indexes are meant to speed up the performance of a database, so use indexing whenever it significantly improves the performance of your database.

As your database becomes larger and larger, the more likely you are to see benefits from indexing.

When not to use Indexes:

  • Indexes should not be used on small tables.

  • Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query’s WHERE clause.

  • Tables that have frequent, large batch update jobs run can be indexed. However, the batch job’s performance is slowed considerably by the index. The conflict can be corrected by dropping the index before the batch job, and then re-creating the index after the job has been completed.

  • Indexes should not be used on columns that contain a high number of NULL values.

  • Columns that are frequently manipulated should not be indexed. Maintenance on the index can become excessive.

For the longest time, what we do is search the table one by one sequentially (page by page).

It’s basically like a full table scan and if the key is also unique you have to scan the whole table anyway, which is very slow.

How to make it fast? How do we make searching one million stuff faster?

Well,

  • Split the table in like n numbers and parallelly process the database in a distributed manner. (Complicated but one way of doing it)

  • Partition the database based on chunk key sizes.

  • Indexing

How removing an index will affect the overall performance?

We have two options – either we can disable or drop the index.

Which is better?

Disabling an Index:

What happens when you do this?

The metadata about the index is retained, as are the usage statistics. The index is not usable by the query optimizer, however.

If you have an index hint in a query specifying the index, the query will fail. Also, foreign key constraints are disabled.

–

Okay, I want it back!

–

How to re-enable a disabled index

If you want to re-enable the index, you need to rebuild it – there is no INDEX…ENABLE command.

–

Dropping an Index:

To drop a clustered or non-clustered index, issue a DROP INDEX command.

Once an index has been dropped, it can’t be rebuilt – it must be created again.

–

Disabling Non-clustered Indexes

vs.

Disabling Clustered Indexes

Disabling a Non-clustered index will deallocate the index pages – the space is freed in the database.

Disabling a Clustered index has additional effects. The data in the table still exists but will be inaccessible for anything other than a drop or rebuild operation.

How do I know if I’m using an Index:

Just add “explain” at the beginning of the query.

Have you faced any issues with the indexes while using the database?

or

It has been an advantage to the database and still using it?

let me know regarding this with your experience using the indexes

db-concepts / linux / flutter-installation / networking / raspberry-pi

–