Exercise: Fast Order Lookups

Create the four indexes that make Riverbend Bike Shop's slow pages fast and its sign-up form safe, and let the tests read each query's explain report, run the search box and try a duplicate email.

Journeyman · MongoDB · Database · Updated

By

Riverbend Bike Shop's pages are slowing down as the orders pile up. My orders reads every order to find one customer's, then sorts them. The returns desk's list of March returns reads every order too. The search box doesn't work at all, and the sign-up form let two customers share one email last week. Each fix is one index, and you'll write all four in mongosh syntax. Tests read what explain("executionStats") reports about each query, run the search, and try a duplicate email.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-mongodb-fast-order-lookups, also linked from the Exercise box beside the lesson. Clone it into your mongodb-course folder. In VS Code, open the mongodb-course folder, open the terminal with Terminal > New Terminal, and type:

git clone https://github.com/ask-codey/exercise-mongodb-fast-order-lookups.git

Git makes a folder, exercise-mongodb-fast-order-lookups, with the exercise code inside. Open that folder in VS Code (File > Open Folder), open its terminal, and install the packages the tests use:

npm install

The folder holds:

  • queries.js: the four functions you'll finish.
  • data/orders.json, data/customers.json and data/products.json: 5,000 of Riverbend's orders, 200 customers and the 12 products, in the same shape as this chapter's lessons.
  • data/load-into-mongosh.js: loads that data into mongosh, so you can try an index there first.
  • test/: the tests, and the helper that runs your statements for them. You don't need to change either.

What to do

Every function in queries.js gets db, which works like it does in mongosh, and returns a find({}) for now, which creates no index. The comment above each function names the query to speed up, or the write to refuse, and says what explain should show when your index is right:

/*
 * 1. My orders. Create the index that makes this query an index scan with no sort in memory:
 *
 *      db.orders.find({ customerId: 42 }).sort({ orderedAt: -1 })
 *
 *    It finds customer 42's 25 orders, newest first: order 4863 (August 31, 2026), then 4663, then 4463.
 *    Today, explain shows COLLSCAN and SORT: MongoDB reads all 5,000 orders, then sorts the 25 in memory.
 *    When your index is right, explain shows IXSCAN and no COLLSCAN or SORT stage, and MongoDB reads 25 orders for
 *    the 25 it returns.
 */
export function indexForMyOrders(db) {
  return db.orders.find({});
}

Replace the find({}) in each function with one createIndex statement of your own, after the word return:

  1. indexForMyOrders: the index that makes db.orders.find({ customerId: 42 }).sort({ orderedAt: -1 }) an index scan with no SORT stage, reading 25 orders for the 25 it returns, as above.
  2. indexForReturnsDesk: the index that makes db.orders.find({ status: "returned", orderedAt: { $gte: ISODate("2026-03-01"), $lt: ISODate("2026-04-01") } }) an index scan that reads 36 orders and 36 index entries for the 36 it returns. (37 index entries also pass, because some versions of MongoDB count one entry past the end of March.)
  3. indexForSearchBox: the index that lets db.products.find({ $text: { $search: "lights" } }) search every product's name and description. It finds 5 products: Beacon Front Light, Beacon Rear Light, Beam 400 Light, Harbor Helmet and Canal City Bike.
  4. uniqueEmail: the index that makes MongoDB refuse db.customers.insertOne({ _id: 201, name: "Maya", email: "maya10@example.com" }), because customer 10 already has that email, with the duplicate key error E11000, while a new customer with a new email, { _id: 202, name: "Jess", email: "jess@example.com" }, still goes in.

Before each test, the data is loaded fresh with only the _id indexes, so an index from one function isn't there for the next.

You can try each index in mongosh first. In the exercise folder's terminal, start mongosh, type use fast_order_lookups to choose a practice database, then load("data/load-into-mongosh.js") to put the data in it. Run a query with .explain("executionStats") on the end, create your index, and run it again. When explain shows what the comment asks for, copy your createIndex into its function after return. Loading the data again removes every index you made, so you can start over.

Check your work

Run the tests from the exercise folder:

npm test

At first every test fails. That's expected: each index you finish turns its test green. When a test fails, its message says what it expected and what explain showed, for example 1) indexForMyOrders: the index finds customer 42's orders, but MongoDB still sorts them in memory (SORT). The index needs the date too, after the customer. Now explain shows SORT, FETCH, IXSCAN: MongoDB read 25 index entries and 25 orders to return 25.

The tests start their own throwaway MongoDB, so they never change your databases. If you installed MongoDB in Setup: MongoDB, mongosh and Compass, they use it. If you didn't, the first npm test downloads MongoDB for the tests, nearly 1 GB on Windows and about 180 MB on a Mac, which can take a few minutes; it's kept for every exercise after this one.

Hints

  • Sort each query's fields first. Which are matched exactly, which one is it sorted by, and which has a range? Compound Indexes and the Order of Fields gives the order to put them in.
  • A SORT stage means the right orders in the wrong order. The index finds them, but MongoDB still has to sort. Reading explain(): COLLSCAN or IXSCAN shows how to spot it.
  • Read the counts. More index entries read than orders returned means the fields are in the wrong order, and more orders read than returned means a field is missing from the index.
  • A search box needs its own kind of index. Unique and Text Indexes shows it, and it lists every field to search in one index.
  • The sign-up index is an ordinary index with one option. The same lesson shows the option.

When you're done

Every test is green, and npm test prints:

✔ 1) indexForMyOrders makes My orders an IXSCAN with no SORT, reading 25 orders for 25 (1297.3954ms)
✔ 2) indexForReturnsDesk makes the March returns an IXSCAN reading 36 orders and 36 or 37 index entries for 36 (88.2105ms)
✔ 3) indexForSearchBox lets a search for lights find the 5 products that mention a light (88.7333ms)
✔ 4) uniqueEmail refuses a second maya10@example.com with E11000 and still lets a new email in (98.1612ms)
ℹ tests 4
ℹ suites 0
ℹ pass 4
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 2043.3227

The times in parentheses will be different on your computer. Now look at your four indexes together. If the returns desk also wanted March's returns biggest first, sorted by total, where would total go in its index, and why?

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

  1. On GitHub, click +, then New repository. Name it (for example exercise-mongodb-fast-order-lookups), choose Public, and click Create repository. Leave out the README: your folder already has one.

  2. In VS Code's terminal, in your exercise folder, send your work to your new repo. Point your copy at your own repo, then commit and push:

    git remote set-url origin https://github.com/your-name/exercise-mongodb-fast-order-lookups.git
    git add .
    git commit -m "My solution"
    git push -u origin main
    
  3. Paste your repo's address, such as https://github.com/your-name/exercise-mongodb-fast-order-lookups, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

A slow page, a broken search box and a duplicate account each came down to one index, and explain showed you the proof. That's how indexing goes on a real app: read the query, choose the index, and check the plan.

Key terms

Related