CODEWITHSUNDEEP
sqldatabase
Henrique Barcelos
Henrique Barcelos

Reputation: 7900

Are there any drawbacks that should hold you from adding an index to a table column?

I've faced this problem two times over the last two weeks: I have to query a huge database, looking for some entries based on a non-numeric field. The problem is that there are no indexes for such fields, so, my query is taking ~40 min to find results (because I limited it to get the only the first 20).

When I asked my co-workers why there is no index on the field, they said that there's no way of guessing a priori which fields will require an index, so, no index was created.

When I asked them to create a new index for me, they said that this cannot be done in production, they'd have to create a test environment, create the index, see how it behaves and only then put this in production. This was considered too much work, unworthed, so, I'm still stuck.

I'm no database expert, but, as far as I know, a new index will only impact the size of the dabatase, nothing else. Am I wrong?

In other words: Why shoul I not create an index on every column that might be a filtering or sorting factor? And why can I not add an index to a production database?

Upvotes: 1

Views: 61

Answers (3)

Greg
Greg

Reputation: 708

Other drawback of adding index to production database is lock on database during its creation (so no new inserts) and longer inserts/updates - every time that data are changed every index need to be updated.

Maybe one time analytical query is not worth a downtime?

Upvotes: 0

Andrew Lewis
Andrew Lewis

Reputation: 5256

Technically, adding an index could slow down updates to the table, or change execution plans, or add overhead when creating the index. In reality though it's doubtful that a simple index would have much of an effect. You're dealing with a political issue, not a technical one.

Upvotes: 1

Kurt Du Bois
Kurt Du Bois

Reputation: 7655

One of the reasons your co-workers won't allow you to add an index on the production database is because adding an index has a cost. It will take a lot of time analyzing an index on a big table.

However as you have already signaled, the index comes with a lot of benefits, so I don't think you should be worrying about the time it takes the rdbms to analyze the table. Adding indexes on columns that are searchable and/or joinable will almost always be a recommended practice, no matter what your co-workers say.

Upvotes: 2

Related Questions