locked
Redundant Index RRS feed

  • Question

  • User779033375 posted

    Hello All,

    In out current DB, we have observed Redundant index.

    e.g

    Index1 : Columns: A, B, C, D 

    Index2: Columns: A,B

    In some cases/tables

    Index3: F G

    Index4: F

    In such case can we delete Index2 and Index4 ?

    Thursday, July 26, 2018 10:25 AM

Answers

  • User753101303 posted

    Just playing but for example (SQL Server) :

    SELECT *
    FROM sys.dm_db_index_usage_stats
    WHERE user_seeks+user_scans+user_lookups=0 AND user_updates>1000

    should tell you which index were updated more than 1000 times but never actually used. 

    Edit : good point. As pointed by Mike you should make sure they are REALLY redundant and then I would have a look at their usage statistics, to compare how they are currently u used.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 12:15 PM
  • User-821857111 posted

    Here is a detailed series of articles on redundant indexes: https://blog.sqlauthority.com/2013/01/04/sql-server-an-interesting-case-of-redundant-indexes-index-on-col1-col2-and-index-on-col1-col2-col3-part-1/

    You should spend a bit of time going through that. It should help you to come to the right solution and to be able to justify your recommendation.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 12:25 PM

All replies

  • User-821857111 posted

    If you don't need the index, yes, you can delete it. 

    Thursday, July 26, 2018 11:31 AM
  • User779033375 posted

    If you don't need the index, yes, you can delete it. 

    Thank you for reply.

    do we have any effect on query Performance if deleted.

    Thursday, July 26, 2018 11:41 AM
  • User475983607 posted

    saffy, only you can answer that question.  We cannot see the database and how it is queried. 

    Thursday, July 26, 2018 11:45 AM
  • User753101303 posted

    Depends on how you access those rows most often. For example if you always use an A, B criteria it could be better to keep index2 and drop index1.

    Ah, in most db you should be able to check index statistics to see how they are used before dropping them. You are using SQL Server ?

    Edsit: for example something like https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-2017 should help to take an informed decision,

    (still add your own knownledge, for example an index could be really useful for a rare operation)

    Thursday, July 26, 2018 11:59 AM
  • User779033375 posted

    saffy, only you can answer that question.  We cannot see the database and how it is queried. 

    I Agree with you, should run query before and after. 

    Want to get experts review from their experience, before I propose this solution.

    Thursday, July 26, 2018 12:06 PM
  • User753101303 posted

    Just playing but for example (SQL Server) :

    SELECT *
    FROM sys.dm_db_index_usage_stats
    WHERE user_seeks+user_scans+user_lookups=0 AND user_updates>1000

    should tell you which index were updated more than 1000 times but never actually used. 

    Edit : good point. As pointed by Mike you should make sure they are REALLY redundant and then I would have a look at their usage statistics, to compare how they are currently u used.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 12:15 PM
  • User-821857111 posted

    Here is a detailed series of articles on redundant indexes: https://blog.sqlauthority.com/2013/01/04/sql-server-an-interesting-case-of-redundant-indexes-index-on-col1-col2-and-index-on-col1-col2-col3-part-1/

    You should spend a bit of time going through that. It should help you to come to the right solution and to be able to justify your recommendation.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 12:25 PM
  • User779033375 posted

    Thank you very much PatriceSc and Mike, for valuable information.

    Tuesday, July 31, 2018 5:56 AM