Menu
I have a need to build five indexes in a large MongoDB collection. I'm familiar with the ensureIndex operation, but I do not know of a way to create all five of the indexes with a single command. Is this batch index creation possible in MongoDB?
526
A good exercise is to create multiple granular indexes (single column index) on a table, build queries using different columns in the where clause, and then view the query plan. Interestingly, SQL Server will often use the indexes as tables to reduce the work load.
SQL Server allows us to create multiple Non-clustered indexes, up to 999 Non-clustered indexes, on each table, with index IDs values assigned to each index starting from 2 for each partition used by the index, as you can find in the sys.
It is one of the most common question about indexing: is it better to create one index for each column or a single index for all columns of a where clause? The answer is very simple in most cases: one index with multiple columns is better—that is, a concatenated or compound index.
To combine multiple indexes, the system scans each needed index and prepares a bitmap in memory giving the locations of table rows that are reported as matching that index's conditions. The bitmaps are then ANDed and ORed together as needed by the query. Finally, the actual table rows are visited and returned.
This is pretty simple within the shell, there is a extention to the collection of createIndexes and you just pass in the keys you wish to create indexes on.
db.test.createIndexes([
{ "a" : 1 },
{ "b" : 1 },
{ "c" : 1 },
{ "d" : 1 },
{ "e" : 1 }
]);
This will then give us the following
> db.test.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.test"
},
{
"v" : 2,
"key" : {
"a" : 1
},
"name" : "a_1",
"ns" : "test.test"
},
{
"v" : 2,
"key" : {
"b" : 1
},
"name" : "b_1",
"ns" : "test.test"
},
{
"v" : 2,
"key" : {
"c" : 1
},
"name" : "c_1",
"ns" : "test.test"
},
{
"v" : 2,
"key" : {
"d" : 1
},
"name" : "d_1",
"ns" : "test.test"
},
{
"v" : 2,
"key" : {
"e" : 1
},
"name" : "e_1",
"ns" : "test.test"
}
]
>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
