Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a downside to adding numerous indexes to tables?

I am creating a new DB and was wondering if there was any downside to adding numerous indexes to tables that I think may require one.

If I create an index but end up not utilizing it will it cause any issues?

like image 417
Jon Avatar asked Feb 03 '10 18:02

Jon


People also ask

Is it good to have multiple indexes on a table?

Yes you can have too many indexes as they do take extra time to insert and update and delete records, but no more than one is not dangerous, it is a requirement to have a system that performs well.

What happens when you have too many indexes on a table?

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.

How many indexes on a table is too many?

To start, I'd say that most tables should have fewer than 15 indexes. In many cases, tables that focus on transaction processing (OLTP) might be in the single digits, whereas tables that are used more for decision support might be well into double digits.


2 Answers

Indexes make it faster to search tables, but longer to write to. Having unused indexes will end come causing some unnecessary slow down.

like image 148
GSto Avatar answered Oct 01 '22 16:10

GSto


Each Index :

  • Takes some place, on disk, and in RAM
  • Takes some time to update, each time you insert/update/delete a row

Which means you should only define indexes that are useful for your application's needs : too many indexes will slow down the writes more than you'll gain from the reads.

like image 17
Pascal MARTIN Avatar answered Oct 01 '22 17:10

Pascal MARTIN