SQL Index - is this overlapping?

Question

I'm going through and removing duplicate and redundant indexes in a SQL database.

So if I have two identical indexes I delete.

If I have overlapping indexes I delete, for example...

• Index 1: brand, model
• Index 2: brand, model, price

I delete index 1.

What about indexes in same order but with extra fields?

• Index 1: brand, model
• Index 2: brand, price, model

Can I safely delete index 1 or not?

My guess was no but I'm not sure

Answer 1

Well, of course you can drop it; you seem to have the permissions. However, the indexes are not equivalent.

For instance, the first will optimize this query:

where brand = @brand and model = @model

The second will be able to use the index, but not optimally, because it cannot seek directly to the right row(s). The engine needs to do a scan of all the @brand records in the index to find the matching models. Because this can be done in the index itself, that should have pretty good performance.

Whether this is important for your queries depends on the nature of the queries, but the indexes are not redundant.

By the way, I think the MySQL documentation does a pretty good job of explaining how composite indexes get used (different databases are not exactly the same in index usage, but they are quite similar).

Answer 2

No, it would not be safe to remove that. The order and position of the columns is important.

The introduction of the price column between the other two columns (brand and model) is the issue. Without the index on just (brand,model), the the model values are "in order" under the brand. With the price column added before model, the model values are no longer "in order".

For example, a query that's doing this:

WHERE brand = 'foo'
  AND model >= 'bar' AND model <= 'cat'

Can make effective use of the index on (brand,model)

It could use an index on (brand,price,model), but it could only do a range scan on the leading column brand. After that, the model values it's looking for could be anywhere under that, under any value of price, so that index is less effective.

If you don't have any queries that are using the index in (brand,model), then it would be safe to drop it.

---

Also, some of these indexes may be "UNIQUE" indexes uses to enforce uniqueness, it's not safe to remove those, unless there's an equivalent index with the same columns in a different order.