Auto Create Index on MongoDB Slow Running Query Community Server

40 views Asked by At

I create this solution for mongoDb to create index on Slow Running Queries(at the moment) finding some references,

First I find the running queries passing the time in mili seconds ("microsecs_running": { "$gte": 30 } ) and store in a variable

use('yourDb');

val=db.adminCommand({ "currentOp": true, "microsecs_running": { "$gte": 30 } , planSummary: 'COLLSCAN'}).inprog;

Then foreach this list in val and print or create directly the index

val.map((v) => {

    if(v.command.find ===undefined){
    return;
    }
    else{

    var _collect=v.command.find;
    var _object_keys=Object.keys(v.command.filter);
    var _object_keys_news;
    
    _object_keys.map((o)=>{
    
    });
    
    var index_content={};
    _object_keys.forEach((v4)=>{
        index_content[v4]= 1
    })
    print(`db.getCollection('${_collect}').createIndex(${JSON.stringify(index_content)})`)
    
    //uncomment next line to directly create the index
    //db.getCollection(_collect).createIndex(index_content) 
    }
    
    })

Example output

-- db.getCollection('table1').createIndex({"col1":1,"col2":1,"col3":1})

-- db.getCollection('table2').createIndex({"col1":1,"col2":1,"col3":1})

Complete command

use('yourDb');

var val=db.adminCommand({ "currentOp": true, "microsecs_running": { "$gte": 30 } , planSummary: 'COLLSCAN'}).inprog;

val.map((v) => {

    if(v.command.find ===undefined){
    return;
    }
    else{

    var _collect=v.command.find;
    var _object_keys=Object.keys(v.command.filter);
    
    var index_content={};
    _object_keys.forEach((v4)=>{
        index_content[v4]= 1
    })
    print(`db.getCollection('${_collect}').createIndex(${JSON.stringify(index_content)})`)
    
    //uncomment next line to directly create the index
    //db.getCollection(_collect).createIndex(index_content)     
}
    
    })

Note: to run this I use mongosh in mongo version 6, i'm no sure if works on previous version

Just Share and Help who's needed

0

There are 0 answers