`Create_index`, MongoDB

If we use databases incorrectly, they will often perform badly. In MongoDB we must create an index on a field we wish to access quickly.

By calling create_index with the correct arguments, we can accelerate calls to find(). The performance gains from indexing can be impressive.

Test program

This program is a bit more complex than some. It tests the performance gain from creating an index on a field in a collection.

Start The program creates a list of 20000 random strings. It inserts these (with insert_one) into the "random_collection."

Next We try to remove the "code" index with drop_index. This will throw an exception if the index is not present.

Info On the second iteration through the for-loop, we create a "code" index. It has unique values.

from pymongo import MongoClient
import random
import time

# Generate 20000 random strings.
list_ids = []
for i in range(0, 20000):
    n = random.randint(0, 1000)
    list_ids.append("Random_" + str(n) + "/" + str(i))

# Connect to database.
client = MongoClient("mongodb://127.0.0.1:27017")
db = client.test_database

# Try without and then with an index.
for test_index in range(0, 2):
    # Reset.
    db.random_collection.delete_many({})
    # Drop index.
    try:
        db.random_collection.drop_index([("code", 1)])
    except:
        print("INDEX NOT DROPPED")
        
    # Create index on second test.
    if test_index == 1:
        print("CREATE INDEX")
        db.random_collection.create_index([("code", 1)], unique=True)

    # Insert documents from random data.
    for random_string in list_ids:
        db.random_collection.insert_one({"code": random_string, "type": "random"})

    # Find inexpensive houses.
    print("FIND")
    print("TIME 1:", time.time())
    # Find values many times.
    count = 0
    for i in range(0, 20000, 20):
        cursor = db.random_collection.find({"code": list_ids[i]})
        # Evaluate the cursor.
        for c in cursor:
            if c != None:
                count += 1
    print("TIME 2:", time.time())FIND
TIME 1: 1509038951.0920522
TIME 2: 1509038959.902277
INDEX NOT DROPPED
CREATE INDEX
FIND
TIME 1: 1509038964.8188376
TIME 2: 1509038965.1191866Time without index: 8.81 s
Time with index:    0.30 s

Notes, find

We try to find values in the "code" field. We must evaluate the returned cursor from find() in order to accurately measure performance.

Notes, results

The index increases the performance of the find() call on the "code." It is more than 20 times faster. For frequent lookups on a field, an index is essential.

Even simple queries in MongoDB benefit from an index (if they are performed often enough). More complex indexes are also available in MongoDB.

Create_index, MongoDB

Test program

Notes, find

Notes, results

`Create_index`, MongoDB