[SERVER-78199] btree index check, avoid create duplicate btree indexes, Avoid affecting performance and increases storage consts. Created: 18/Jun/23  Updated: 09/Jan/24  Resolved: 09/Jan/24

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: y yz Assignee: Daotang Yang
Resolution: Duplicate Votes: 0
Labels: former-storex-namer
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2023-06-18-15-32-00-192.png     PNG File image-2023-06-18-15-41-49-632.png     PNG File image-2023-06-18-15-42-34-344.png    
Issue Links:
Backports
backported by SERVER-84667 index check, avoid create duplicate b... Closed
Related
is related to SERVER-27464 Server allows creation of duplicate i... Backlog
Assigned Teams:
Storage Execution
Operating System: ALL
Sprint: Execution Team 2023-12-11, Execution Team 2024-01-08, Execution Team 2024-01-22
Participants:

 Description   

btree index check, avoid create duplicate btree indexes, Avoid affecting performance

btree index check, avoid create duplicate btree indexes, Avoid affecting performance by the deplicate index.
for example:
add two index: db.collection.createIndex({a:1}) and db.collection.createIndex({a:11})
the tow index are actually the same, One of them is a useless index, but it can affect the insert performance.

In addition, They also affecting query performance because both are candidate indexes. This increases storage consts.



 Comments   
Comment by y yz [ 09/Jan/24 ]

hi, daotang.yang@mongodb.com 

master branch PR: SERVER-84667

Comment by Daotang Yang [ 08/Jan/24 ]

1147952115@qq.com Please change the pull request against master instead of 5.0

Comment by Louis Williams [ 13/Oct/23 ]

Hi 1147952115@qq.com, thanks for the PR. We are looking for someone to evaluate your PR.

Comment by y yz [ 13/Oct/23 ]

hi, Chris Kelly

Do we have the conclusion of this PR? 

thanks.

Comment by Chris Kelly [ 20/Jun/23 ]

Thanks for the pull request 1147952115@qq.com  - I'll pass this to the relevant team to take a look.

Your continued efforts here are appreciated!

Christopher

Comment by y yz [ 18/Jun/23 ]

the code address:  https://github.com/mongodb/mongo/pull/1557

Comment by y yz [ 18/Jun/23 ]

befor:

 

after the PR push:

Comment by y yz [ 18/Jun/23 ]

switched to db test
mongodb_5.0_shard2:PRIMARY> db.test.createIndex({a:1})

{         "numIndexesBefore" : 1,         "numIndexesAfter" : 2,         "createdCollectionAutomatically" : true,         "commitQuorum" : "votingMembers",         "ok" : 1,         xxx         "operationTime" : Timestamp(1687072400, 2) }

mongodb_5.0_shard2:PRIMARY> 
mongodb_5.0_shard2:PRIMARY> 
mongodb_5.0_shard2:PRIMARY> db.test.createIndex({a:11})

{         "numIndexesBefore" : 2,         "numIndexesAfter" : 3,         "createdCollectionAutomatically" : false,         "commitQuorum" : "votingMembers",         "ok" : 1,         xxxx         "operationTime" : Timestamp(1687072403, 1) }

mongodb_5.0_shard2:PRIMARY> 
mongodb_5.0_shard2:PRIMARY> db.test.getIndexes()
[
        {
                "v" : 2,
                "key" :

{                         "_id" : 1                 }

,
                "name" : "id"
        },
        {
                "v" : 2,
                "key" :

{                         "a" : 1                 }

,
                "name" : "a_1"
        },
        {
                "v" : 2,
                "key" :

{                         "a" : 11                 }

,
                "name" : "a_11"
        }
]

Generated at Thu Feb 08 06:37:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.