In the first post of this series, I've imported a sample dataset, and I'll show how adding a few indexes can open performance to new use cases. Before looking at regular indexes for OLTP, I'll show that it is easy to create one Search Index for on demand reporting queries.
With Search Index: OLAP Ad-hoc queries for reporting
For analytic reporting, the best is to create an Atlas Search index that is maintained asynchronously, isolated from the operational workload, for near-real-time queries. I describe the columns I want to index:
db.youstats.createSearchIndex(
"SearchYoustats", {
mappings: {
dynamic: false,
fields: {
type: { type: "token" },
duration: { type: "number" },
commentsNumber: { type: "number" },
publishedDate: { type: "token" },
category: { type: "token" },
title: { type: "string" },
description: { type: "string" },
author: { type: "string" },
views: {
type: "document",
fields: {
daily: {
type: "document",
fields: {
data: { type: "number" }
}
}
}
}
}
}
}
);
No need to know the access patterns in advance, just list the fields you want to index without thinking of an ordered key.
To find videos in the "Games" category that are longer than 30 seconds and have been published recently (I want only the Top-10), here’s an efficient query on this index:
db.youstats.aggregate([
{
$search: {
index: "SearchYoustats",
compound: {
filter: [
{ equals: { path: "category", value: "Music" } },
{ range: { path: "duration", gt: 30 } }
]
},
sort: {
"publishedDate": -1
}
}
},
{
$limit: 10
},
{
$project: {
_id: 0,
author: 1,
title: 1,
duration: 1,
type: 1,
publishedDate: 1
}
}
]);
The same index can also be used for text search, for example looking for "MongoDB" in the title:
db.youstats.aggregate([
{
$search: {
index: "SearchYoustats",
text: {
query: "MongoDB", // The search term
path: "description" // Targeting the 'description' field
}
}
},
{
$group: {
_id: "$category", // Group by category
count: { $sum: 1 }, // Count the number of documents in each category
totalDuration: { $sum: "$duration" } // Sum the duration for each category
}
},
{
$sort: { totalDuration: -1 } // Sort by total duration in descending order
}
]);
{ _id: 'Tech', count: 2, totalDuration: 308 },
{ _id: 'Education', count: 1, totalDuration: 118 }
Many new use cases can simply be served by Atlas Index Search with simplicity. This is perfect for analytic queries for reporting that must not impact the operational workloads. However, for OLTP systems that require querying the current state based on specific access patterns, it's essential to create dedicated indexes. We will cover that in the next posts.
Top comments (0)