Mongodb $in Query Statement Performance Optimization

  mongodb, question

Data: About 6.5 million articles. The actual query took 12 seconds. The indexes needed to be done were all done. 12 seconds is too long.
Query statement:

db.attach.find({ data_id:
 { '$in':
 [ ObjectId('595b7d3c0b192c196eb84f87'),
 ObjectId('595b7d29a2a75c18af7eff2d'),
 ObjectId('595b7d21adb12b0f86224acc'),
 ObjectId('595b7d120031d40f881634b8'),
 ObjectId('595b7d2c88ce6d18afd34dab'),
 ObjectId('595b7d2a36f526176ba21156'),
 ObjectId('595b7d1b47c347196d6d26f5'),
 ObjectId('595b7d1372321718a937f649'),
 ObjectId('595b7d25fda0d80f289ed24b'),
 ObjectId('595b7d28ad1a8118a85ff575'),
 ObjectId('595b7d268d9cfb17b285dff3'),
 ObjectId('595b7d18adb12b0f8622472d'),
 ObjectId('595b7d130630360f7f8ded79'),
 ObjectId('595b7d22b5077e1a668c189d'),
 ObjectId('595b7d23ab191119de23b9a3'),
 ObjectId('595b7d1eadb12b0f86224a6d'),
 ObjectId('595b7d1c72cf8718a10beec9'),
 ObjectId('595b7d10ad1a8118a85fed1e'),
 ObjectId('595b7d218c7ff00f9d1ebe2f'),
 ObjectId('595b7d2547c347196d6d2a80'),
 ObjectId('595b7d27c7ed9e0f13402dc3'),
 ObjectId('595b7d18e184ae18b5bfe689'),
 ObjectId('595b7d178c7ff00f9d1eba66'),
 ObjectId('595b7d198d9cfb17b285d8a1'),
 ObjectId('595b7d0ffda0d80f289ec90b') ] }}).explain()

Results of implementation:

[
    {
        queryPlanner: {
            plannerVersion: 1,
            namespace: "admin.attach",
            indexFilterSet: false,
            parsedQuery: {
                data_id: {
                    $in: [
                        ObjectId("595b7d0ffda0d80f289ec90b"),
                        ObjectId("595b7d10ad1a8118a85fed1e"),
                        ObjectId("595b7d120031d40f881634b8"),
                        ObjectId("595b7d130630360f7f8ded79"),
                        ObjectId("595b7d1372321718a937f649"),
                        ObjectId("595b7d178c7ff00f9d1eba66"),
                        ObjectId("595b7d18adb12b0f8622472d"),
                        ObjectId("595b7d18e184ae18b5bfe689"),
                        ObjectId("595b7d198d9cfb17b285d8a1"),
                        ObjectId("595b7d1b47c347196d6d26f5"),
                        ObjectId("595b7d1c72cf8718a10beec9"),
                        ObjectId("595b7d1eadb12b0f86224a6d"),
                        ObjectId("595b7d218c7ff00f9d1ebe2f"),
                        ObjectId("595b7d21adb12b0f86224acc"),
                        ObjectId("595b7d22b5077e1a668c189d"),
                        ObjectId("595b7d23ab191119de23b9a3"),
                        ObjectId("595b7d2547c347196d6d2a80"),
                        ObjectId("595b7d25fda0d80f289ed24b"),
                        ObjectId("595b7d268d9cfb17b285dff3"),
                        ObjectId("595b7d27c7ed9e0f13402dc3"),
                        ObjectId("595b7d28ad1a8118a85ff575"),
                        ObjectId("595b7d29a2a75c18af7eff2d"),
                        ObjectId("595b7d2a36f526176ba21156"),
                        ObjectId("595b7d2c88ce6d18afd34dab"),
                        ObjectId("595b7d3c0b192c196eb84f87")
                    ]
                }
            },
            winningPlan: {
                stage: "FETCH",
                inputStage: {
                    stage: "IXSCAN",
                    keyPattern: {
                        data_id: 1.0
                    },
                    indexName: "data_id_1.0",
                    isMultiKey: false,
                    isUnique: false,
                    isSparse: false,
                    isPartial: false,
                    indexVersion: 1,
                    direction: "forward",
                    indexBounds: {
                        data_id: [
                            "[ObjectId('595b7d0ffda0d80f289ec90b'), ObjectId('595b7d0ffda0d80f289ec90b')]",
                            "[ObjectId('595b7d10ad1a8118a85fed1e'), ObjectId('595b7d10ad1a8118a85fed1e')]",
                            "[ObjectId('595b7d120031d40f881634b8'), ObjectId('595b7d120031d40f881634b8')]",
                            "[ObjectId('595b7d130630360f7f8ded79'), ObjectId('595b7d130630360f7f8ded79')]",
                            "[ObjectId('595b7d1372321718a937f649'), ObjectId('595b7d1372321718a937f649')]",
                            "[ObjectId('595b7d178c7ff00f9d1eba66'), ObjectId('595b7d178c7ff00f9d1eba66')]",
                            "[ObjectId('595b7d18adb12b0f8622472d'), ObjectId('595b7d18adb12b0f8622472d')]",
                            "[ObjectId('595b7d18e184ae18b5bfe689'), ObjectId('595b7d18e184ae18b5bfe689')]",
                            "[ObjectId('595b7d198d9cfb17b285d8a1'), ObjectId('595b7d198d9cfb17b285d8a1')]",
                            "[ObjectId('595b7d1b47c347196d6d26f5'), ObjectId('595b7d1b47c347196d6d26f5')]",
                            "[ObjectId('595b7d1c72cf8718a10beec9'), ObjectId('595b7d1c72cf8718a10beec9')]",
                            "[ObjectId('595b7d1eadb12b0f86224a6d'), ObjectId('595b7d1eadb12b0f86224a6d')]",
                            "[ObjectId('595b7d218c7ff00f9d1ebe2f'), ObjectId('595b7d218c7ff00f9d1ebe2f')]",
                            "[ObjectId('595b7d21adb12b0f86224acc'), ObjectId('595b7d21adb12b0f86224acc')]",
                            "[ObjectId('595b7d22b5077e1a668c189d'), ObjectId('595b7d22b5077e1a668c189d')]",
                            "[ObjectId('595b7d23ab191119de23b9a3'), ObjectId('595b7d23ab191119de23b9a3')]",
                            "[ObjectId('595b7d2547c347196d6d2a80'), ObjectId('595b7d2547c347196d6d2a80')]",
                            "[ObjectId('595b7d25fda0d80f289ed24b'), ObjectId('595b7d25fda0d80f289ed24b')]",
                            "[ObjectId('595b7d268d9cfb17b285dff3'), ObjectId('595b7d268d9cfb17b285dff3')]",
                            "[ObjectId('595b7d27c7ed9e0f13402dc3'), ObjectId('595b7d27c7ed9e0f13402dc3')]",
                            "[ObjectId('595b7d28ad1a8118a85ff575'), ObjectId('595b7d28ad1a8118a85ff575')]",
                            "[ObjectId('595b7d29a2a75c18af7eff2d'), ObjectId('595b7d29a2a75c18af7eff2d')]",
                            "[ObjectId('595b7d2a36f526176ba21156'), ObjectId('595b7d2a36f526176ba21156')]",
                            "[ObjectId('595b7d2c88ce6d18afd34dab'), ObjectId('595b7d2c88ce6d18afd34dab')]",
                            "[ObjectId('595b7d3c0b192c196eb84f87'), ObjectId('595b7d3c0b192c196eb84f87')]"
                        ]
                    }
                }
            },
            rejectedPlans: [
            ]
        },
        executionStats: {
            executionSuccess: true,
            nReturned: 14842,
            executionTimeMillis: 5881,
            totalKeysExamined: 14867,
            totalDocsExamined: 14842,
            executionStages: {
                stage: "FETCH",
                nReturned: 14842,
                executionTimeMillisEstimate: 20,
                works: 14867,
                advanced: 14842,
                needTime: 24,
                needYield: 0,
                saveState: 116,
                restoreState: 116,
                isEOF: 1,
                invalidates: 0,
                docsExamined: 14842,
                alreadyHasObj: 0,
                inputStage: {
                    stage: "IXSCAN",
                    nReturned: 14842,
                    executionTimeMillisEstimate: 10,
                    works: 14867,
                    advanced: 14842,
                    needTime: 24,
                    needYield: 0,
                    saveState: 116,
                    restoreState: 116,
                    isEOF: 1,
                    invalidates: 0,
                    keyPattern: {
                        data_id: 1.0
                    },
                    indexName: "data_id_1.0",
                    isMultiKey: false,
                    isUnique: false,
                    isSparse: false,
                    isPartial: false,
                    indexVersion: 1,
                    direction: "forward",
                    indexBounds: {
                        data_id: [
                            "[ObjectId('595b7d0ffda0d80f289ec90b'), ObjectId('595b7d0ffda0d80f289ec90b')]",
                            "[ObjectId('595b7d10ad1a8118a85fed1e'), ObjectId('595b7d10ad1a8118a85fed1e')]",
                            "[ObjectId('595b7d120031d40f881634b8'), ObjectId('595b7d120031d40f881634b8')]",
                            "[ObjectId('595b7d130630360f7f8ded79'), ObjectId('595b7d130630360f7f8ded79')]",
                            "[ObjectId('595b7d1372321718a937f649'), ObjectId('595b7d1372321718a937f649')]",
                            "[ObjectId('595b7d178c7ff00f9d1eba66'), ObjectId('595b7d178c7ff00f9d1eba66')]",
                            "[ObjectId('595b7d18adb12b0f8622472d'), ObjectId('595b7d18adb12b0f8622472d')]",
                            "[ObjectId('595b7d18e184ae18b5bfe689'), ObjectId('595b7d18e184ae18b5bfe689')]",
                            "[ObjectId('595b7d198d9cfb17b285d8a1'), ObjectId('595b7d198d9cfb17b285d8a1')]",
                            "[ObjectId('595b7d1b47c347196d6d26f5'), ObjectId('595b7d1b47c347196d6d26f5')]",
                            "[ObjectId('595b7d1c72cf8718a10beec9'), ObjectId('595b7d1c72cf8718a10beec9')]",
                            "[ObjectId('595b7d1eadb12b0f86224a6d'), ObjectId('595b7d1eadb12b0f86224a6d')]",
                            "[ObjectId('595b7d218c7ff00f9d1ebe2f'), ObjectId('595b7d218c7ff00f9d1ebe2f')]",
                            "[ObjectId('595b7d21adb12b0f86224acc'), ObjectId('595b7d21adb12b0f86224acc')]",
                            "[ObjectId('595b7d22b5077e1a668c189d'), ObjectId('595b7d22b5077e1a668c189d')]",
                            "[ObjectId('595b7d23ab191119de23b9a3'), ObjectId('595b7d23ab191119de23b9a3')]",
                            "[ObjectId('595b7d2547c347196d6d2a80'), ObjectId('595b7d2547c347196d6d2a80')]",
                            "[ObjectId('595b7d25fda0d80f289ed24b'), ObjectId('595b7d25fda0d80f289ed24b')]",
                            "[ObjectId('595b7d268d9cfb17b285dff3'), ObjectId('595b7d268d9cfb17b285dff3')]",
                            "[ObjectId('595b7d27c7ed9e0f13402dc3'), ObjectId('595b7d27c7ed9e0f13402dc3')]",
                            "[ObjectId('595b7d28ad1a8118a85ff575'), ObjectId('595b7d28ad1a8118a85ff575')]",
                            "[ObjectId('595b7d29a2a75c18af7eff2d'), ObjectId('595b7d29a2a75c18af7eff2d')]",
                            "[ObjectId('595b7d2a36f526176ba21156'), ObjectId('595b7d2a36f526176ba21156')]",
                            "[ObjectId('595b7d2c88ce6d18afd34dab'), ObjectId('595b7d2c88ce6d18afd34dab')]",
                            "[ObjectId('595b7d3c0b192c196eb84f87'), ObjectId('595b7d3c0b192c196eb84f87')]"
                        ]
                    },
                    keysExamined: 14867,
                    dupsTested: 0,
                    dupsDropped: 0,
                    seenInvalidated: 0
                }
            },
            allPlansExecution: [
            ]
        }
    }
]

It seems that there is no need for optimization. I inquired on the server that the value of executionTimeMillis was just 5000 and now 24. Almost seconds passed.

I really doubt that the speed is slow, just because of network problems. After all, 10,000 were found. The data transmission between the database and the server through the network is slow, not because the query is slow, but because the data transmission is slow. . .
Of course, this is only my guess.