While it's theoretically possible to rewrite, there's nothing in the documentation or plan to indicate any query rewrites. The aggregation pipeline is procedural and verbose. MongoDB's queries are simplistic in the find(), save(), remove(), and update() methods. Let's see how MongoDB's optimizer handles these. MongoDB's query language is simplistic even if it's trying to mimic the SQL operations. This is a symptom of a larger problem in the MongoDB optimizer it makes ad-hoc decisions resulting in restrictions like this. If you restrict to one text index, the choice is easy. Is it a problem with the optimizer? When you have multiple indexes, the optimizer will have to choose the right index for the query.The size of the index increases linearly to the number of word indexes and not the number of documents. Its size can grow exponentially when you use an array index. Is it the size of the text indexes? Text indexes generate an array of tokens on text and index them.Is it the number of the text indexes? Search indexes are typically built with an inverted-tree data structure, but MongoDB has chosen to build it with the B-Tree index.Why Does MongoDB Text Index Is Restricted to One Index per Collection? You can create multiple indexes on scalars in MongoDB.When you search for the shipping order ID, you don't want to see the returned order ID. Here's the problem: when you search for customer address, you don't want to see the shipping address. Having a single search index means you need to create a single VERY LARGE index combining all the fields you'd ever want to search. So, a single customer document can contain customer information, customers orders, customer shipping information, and customer billing information. so the majority of your operations happen on a single (JSON) document. MongoDB and other NoSQL databases encourage you to denormalize (aggregate) your schema so you create a single large document representing an object: a customer, a partner, etc.Why Should You Care About This Restriction? For this article, explaining this one restriction will suffice. Depending on the index selected, choose the predicates to push down, see the query is covered or not, decide on sort and pagination strategy.Ĭonsider the case of MongoDB restriction." A collection can have at most one text index." It documents a few other restrictions along with this.Selecting the optimal index(es) for each of the table (keyspaces in Couchbase N1QL, collection in case of MongoDB).Query rewrite based on heuristics, cost or both.At a very high level, SQL optimizers decide the following before creating the execution tree: Database OptimizerĪ query optimizer chooses an optimal index and access paths to execute the query. When you don't have a good optimizer, developers have to live with feature restrictions and DBAs have to live with performance issues. Now, most NoSQL databases have SQL-like query language support. The cost of Memory, CPU, disk usage is added to the cost of a plan in a cost-based optimizer. Efficiency is measured in latency and throughput, depending on the workload. The Query optimizer evaluates the possibilities and selects the efficient plan. Queries can be executed in many different ways. The next day, when he told his wife he's going to the corner store, she asked him, "Are you taking the short route or the long one?" Note that hinting will ignore any future indexes added that might be more ideal, and will also fail with an exception if the in-memory sort needs to work with more than 32MB of data.Īdding the suggested compound index would result in the most efficient ratio of keys and docs examined to the number of results returned.A man returned home after walking around the globe for eleven years. So why for same query, we don't have same execution plan ?Įxcerpt of mongod logfile : T13:55:28.282+0100 I COMMAND command mydb.products command: find )Īlternatively (and less ideally) you could provide an index hint to force the query to use the first index. However, when I run the same queries by myself in mongo shell, the correct index is used. Most of them are not using the best index and consequently the best execution plan is not used. In production mongod logfile, we can see a lot of slow queries.