Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What are the disadvantages to Indexes in database tables?

Tags:

Is there any reason I shouldn't create an index for every one of my database tables, as a means of increasing performance? It would seem there must be some reason(s) else all tables would automatically have one by default.

I use MS SQL Server 2016.

like image 376
Stpete111 Avatar asked Dec 31 '16 17:12

Stpete111


People also ask

What is the disadvantages of indexes in database?

The downside to adding indexes to a table is that they affect the performance of writes. Moreover, improperly created indexes can even adversely affect SELECT queries! Any table configuration where performance suffers due to excessive, improper, or missing indexes is considered to be poor indexing.

What are disadvantages of using indexes in MySQL?

The Drawbacks of Using IndexesIndexes consume disk space – an index occupies its own space, so indexed data will consume more disk space too; Redundant and duplicate indexes can be a problem – MySQL allows you to create duplicate indexes on a column and it does not “protect you” from doing such a mistake.

What are the problems with having too many indexes?

Too many indexes create additional overhead associated with the extra amount of data pages that the Query Optimizer needs to go through. Also, too many indexes require too much space and add to the time it takes to accomplish maintenance tasks.


1 Answers

One index on a table is not a big deal. You automatically have an index on columns (or combinations of columns) that are primary keys or declared as unique.

There is some overhead to an index. 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. When data is inserted/updated/deleted, then 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.

A small number of indexes on each table are reasonable. These should be designed with the typical query load in mind. 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.

like image 188
Gordon Linoff Avatar answered Oct 13 '22 23:10

Gordon Linoff