Can someone please help me? I’m working on an Android application and trying to add an index to a Couchbase Lite collection. However, when I run a query, it seems like the query actually takes slightly longer with the index compared to when there’s no index. Has anyone encountered this before or know what might be causing it?
What query and what index are we talking about?
The query is a standard search query.
I am trying to index on a meta property.
meta.id() is the key of the b-tree that holds the documents; is the primary key. So an index on it will be created at the time you create the collection in question, no need to do it manually.
Can you please provide more information about the slightly longer time you are observing. What is the query.explain() looking like in both scenario? Maybe an example of such?
What query?
What meta property?
Apologies for the confusion the meta I’m referring to is just a regular property within our JSON document, not the Couchbase metadata.
Query:
SELECT fl_result(fl_root(hotel.body)) FROM kv_default AS hotel WHERE
((fl_value(hotel.body, ‘meta.type’) = ‘hotel_booking’ AND
fl_value(hotel.body, ‘payload.hotelId’) = ‘hotel_18hqslnd3cdnk’)
AND fl_value(hotel.body, ‘payload.status’) = ‘PLANNED’) AND (hotel.flags & 1 = 0)
3|0|0| SEARCH hotel USING INDEX TestIndex (=?)
{“FROM”:[{“COLLECTION”:“_default._default”,“AS”:“hotel”}],“WHAT”:[[“.”]],
“WHERE”:[“AND”,[“AND”,[“=”,[“.meta.type”],“hotel_booking”],[“=”,[“.payload.hotelId”],
“hotel_18hqslnd3cdnk”]],[“=”,[“.payload.status”],“PLANNED”]]}
Apologies for the confusion the meta I’m referring to is just a regular property within our JSON document, not the Couchbase metadata.
Query:
SELECT fl_result(fl_root(hotel.body)) FROM kv_default AS hotel WHERE
((fl_value(hotel.body, ‘meta.type’) = ‘hotel_booking’ AND
fl_value(hotel.body, ‘payload.hotelId’) = ‘hotel_18hqslnd3cdnk’)
AND fl_value(hotel.body, ‘payload.status’) = ‘PLANNED’) AND (hotel.flags & 1 = 0)
3|0|0| SEARCH hotel USING INDEX TestIndex (=?)
{“FROM”:[{“COLLECTION”:“_default._default”,“AS”:“hotel”}],“WHAT”:[[“.”]],
“WHERE”:[“AND”,[“AND”,[“=”,[“.meta.type”],“hotel_booking”],[“=”,[“.payload.hotelId”],
“hotel_18hqslnd3cdnk”]],[“=”,[“.payload.status”],“PLANNED”]]}
From 3|0|0| SEARCH hotel USING INDEX TestIndex (=?), seems like this is a covering index; all fields are present in the index. You might want to split it in multiple indexes or change the index fields order, from high selectivity to low. If all the hotel body is indexed, depending on the dataset size, it might actually be slower in some cases, than a full scan.
If this is still an issue, as we are talking about performance, we need some numbers to move forward. How much of a slowness are we talking about here? What is dataset size? How big is a document? What about the query parameters you are using (are those simple or include some more complex logic)?