Thannks for the explanation
Hello @abhinav ,
for some unknow reason the index was becomming 3 times larger than the data, I did some changes to the index (disabling dovalue and include term vectors). Now for some reasons, the quesy that we did together are not working anymore…
Can you please gave a look at the index and let me know what I am doing wrong?
{
"type": "fulltext-index",
"name": "product_name",
"uuid": "784b2727c74866a2",
"sourceType": "couchbase",
"sourceName": "app-live",
"planParams": {
"maxPartitionsPerPIndex": 171,
"indexPartitions": 6
},
"params": {
"doc_config": {
"docid_prefix_delim": "",
"docid_regexp": "",
"mode": "type_field",
"type_field": "sub_type"
},
"mapping": {
"analysis": {},
"default_analyzer": "standard",
"default_datetime_parser": "dateTimeOptional",
"default_field": "_all",
"default_mapping": {
"dynamic": true,
"enabled": false
},
"default_type": "_default",
"docvalues_dynamic": true,
"index_dynamic": true,
"store_dynamic": false,
"type_field": "_type",
"types": {
"product": {
"dynamic": false,
"enabled": true,
"properties": {
"product_a_store": {
"dynamic": false,
"enabled": true,
"properties": {
"product_a_id": {
"dynamic": false,
"enabled": true,
"fields": [
{
"include_in_all": true,
"include_term_vectors": true,
"index": true,
"name": "product_a_id",
"type": "number"
}
]
},
"product_name": {
"dynamic": false,
"enabled": true,
"fields": [
{
"include_in_all": true,
"index": true,
"name": "product_name",
"type": "text"
}
]
},
"product_primary_genre_id": {
"dynamic": false,
"enabled": true,
"fields": [
{
"include_term_vectors": true,
"index": true,
"name": "product_primary_genre_id",
"type": "number"
}
]
},
"product_provider_name": {
"dynamic": false,
"enabled": true,
"fields": [
{
"include_in_all": true,
"index": true,
"name": "product_provider_name",
"type": "text"
}
]
},
"product_seller_name": {
"dynamic": false,
"enabled": true,
"fields": [
{
"include_in_all": true,
"index": true,
"name": "product_seller_name",
"type": "text"
}
]
},
"bundle_id": {
"dynamic": false,
"enabled": true,
"fields": [
{
"include_in_all": true,
"index": true,
"name": "bundle_id",
"type": "text"
}
]
}
}
}
}
}
}
},
"store": {
"indexType": "scorch"
}
},
"sourceParams": {}
}
The query I am trying to run is:
SELECT meta().id
FROM `app-live` AS a
WHERE sub_type="product"
AND SEARCH(a, "dark");
The “Explain” looks like this:
so, somehow it seems that the index is not used
The index definition looks fine to me and so does the query.
Seems like your query is picking up a GSI (Global Secondary index) that you have defined which fits the criteria. Try forcing the query to use an FTS index if available -
SELECT meta().id
FROM `app-live` AS a USE INDEX(USING FTS)
WHERE sub_type="product"
AND SEARCH(a, "dark");
It doesn’t work forcing the FTS index.
but I discovered that if I enable the _all option on one specific field, suddenly it starts to work correctly… so… I think this may be a bug.
We can look into this more in depth if you want as I have an Enterprise license and I can give you acces on the cluster.
In the index definition you’ve shared, it does have “Include in _all field” over most fields.
Since you’re not specifying the field in your query, you will need to enable the “Include in _all field” option for at least one field for your query to choose the index. This is expected behavior.
I have multiple fields with _all and the ones which are not are specified in the query, still don’t work
I’m not sure I follow what you’re trying to tell me - you’re not specifying any field in your query.
With the query string you’re trying to run here’s how you can field scope your search …
SELECT meta().id
FROM `app-live` AS a USE INDEX(USING FTS)
WHERE sub_type="product"
AND SEARCH(a, "product_a_store.product_name:dark");
SELECT meta().id
FROM `app-live` AS a USE INDEX(USING FTS)
WHERE sub_type="product"
AND SEARCH(a, {"match":"dark", "field":"product_a_store.product_name");
These queries will look for the term “dark” within the field product_name of your mapping product_a_store. If you want to look for a term across multiple fields, you can provide a disjunction query.
the examples I gave you are minimal, as this is a public forum and I cannot publish all the details. But yes, I tried in the way you are mentioning
I understand. Feel free to reach out to Couchbase support anytime for this.
We’ll be able to assist you in defining your index better as well as the queries once we get a good sense of your use case.
ok, I willdo this tomorrow. thanks for your pacience with me