-1

I have 2 sql indexes on table, like:

  1. idx_Inventory_One (SupplierId, PartId)
  2. idx_Inventory_Two (PartId, SupplierId)

So, what is the difference between them, should I remove any one and can I have both of them ?

minhhungit
  • 180
  • 4
  • 25
  • This post shows no research effort on your part. Go do a few Google searches on how indexes function and what impact the column order has on queries. – dfundako Dec 29 '16 at 21:39
  • @Siyual: it's not my question – minhhungit Dec 29 '16 at 21:49
  • @NgàiJin No, but it answers your question. – Siyual Dec 29 '16 at 21:50
  • @siyual: so should I keep both of them ? and if I keep, will it make harm – minhhungit Dec 29 '16 at 21:51
  • 1
    @NgàiJin It really just depends on how you're using the indexes. If you're unsure, [check the usage stats of the indexes](http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/amp/) to determine which (if any) you should drop. – Siyual Dec 29 '16 at 21:56

1 Answers1

3

Indexes do no harm. They cost some memory and a little extra time on insert, update and delete, but this is usually neglectable.

The order of columns in an index can make a difference.

 select supplierid from mytable where partid = 5;

Here we need an index that starts with partid in order to find the desired records. An index that starts with supplierid would be of little or no use for this query.

 select partid from mytable where supplierid = 123;

Here, on the other hand, we need an index starting with supplierid in order to quickly find the desired records.

 select * from mytable where partid = 5 and supplierid = 123;

And for this query either index should be fine. The DBMS will probably pick the one it thinks more appropriate according to columns' selectivity. That means if there are thousand records with partid = 5, but only ten records with supplierid = 123, it will probably use the index starting with supplierid as to get the ten records and check which of these ten have partid = 5, than to have to read thousand partid 5 index entries in order to check whether they have supplierid = 123.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73