I have a select query and I have created an index for the query. However, when I run the query with the explain statement it finds a different index, other than the index, I created for the query. For example:
CREATE INDEX index_1 ON write(docType) WHERE (docType = “test”)
CREATE INDEX index_2 ON write(id, name, 'docType) WHERE (docType = “test”)
SELECT id, name from write where docType = ‘test’
The explain finds index_1 and not index_2
I cannot drop index_1.
Is there any other way, other than using the use index clause, in the select query and specifying the index I created for the query, for the query to use the index?
Any help or feedback would be greatly appreciated.
index_2 does not qualify because index_2 has leading index key as id and query does not have predicate on id so it can’t use it. If used it will give wrong results. That is why index_2 does not qualify.
Example:
INSERT INTO default VALUES ("k01",{"docType":"test"});
INSERT INTO default VALUES ("k02",{"docType":"test", "id":"k02"});
INSERT INTO default VALUES ("k03",{"docType":"test","id":"k03","name":"xyz"});
CREATE INDEX index_1 ON default(docType) WHERE (docType = "test");
CREATE INDEX index_2 ON default(id, name, docType) WHERE (docType = "test");
SELECT id, name from default where docType = "test";
As query has predicate on docType and based on query it should return 3 document
When leading index key is MISSING indexer will not index that document (Couchbase bucket can have any arbitrary documents, indexer uses this strategy not to blow up indexes).
If it used index_3 it will give 2 documents only (i.e. “k02”, “k03”)
You can rewrite query like this if that is acceptable based on your needs.
SELECT id, name from default where docType = "test" AND id IS NOT MISSING;
SELECT id, name from default where docType = "test" AND id IS NOT NULL;
CREATE INDEX index_2 ON default(id, name, docType) WHERE (docType = "test");
SELECT id, name from default where docType = "test" AND id IS NOT MISSING;
SELECT id, name from default where docType = "test" AND id IS NOT NULL;
Above queries uses index_2 and gives 2 documents.
CREATE INDEX index_1 ON default(docType) WHERE (docType = "test");
SELECT id, name from default where docType = "test";
Above query uses index_1 and can’t use index_2 and gives 3 documents.
If the CREATE INDEX says WHERE (docType = “test”) then every document with docType=“test” should be indexed. For doc k01, let the indexed value be null (since it does not have the id attribute).
But k01 passes the WHERE clause of CREATE INDEX, so, to me atleast, it is very misleading to not index it. And I cannot understand that why not index it? If k01 does not have the “id” attribute, then “id” can be NULL in the index data structure. k02 has “id” but does not have “name” but still it will be a part of index which is created as below:
CREATE INDEX index_2 ON default(id, name, docType) WHERE (docType = “test”);
Which means “name” will be NULL in the index for k02. On similar lines, why not index k01?
Thanks for the workaround and all the responses so far.
However, I strongly feel that its non-intuitive. Definitely so, for somebody coming from the RDBMS world.
Consider the below perspective:
a user/developer creates an index on ID attribute with WHERE docType=“test”. When the index is created, he feels/assumes/believes that the index covers all docs that have docType=“test” whereas that is NOT the case.
He then runs his queries which uses the index but he never realizes that the results are not for all docs with docType=“test”. Its like WRONG!!! And he will never ever realize that - thats a big issue. An error is better than something going wrong silently, isnt it?
N1QL query uses the index only when it qualifies i.e it can give the results correctly. If doesn’t qualify it never uses. Choosing index result in wrong results that index will never be chosen.
More details can be found in DESIGNING INDEX FOR QUERY IN COUCHBASE N1QL