I’m on a task to remove the primary index out of some spring boot based couchbase repositories. Currently, the repositories are using @Repository, @N1qlPrimaryIndexed annotation, and primary index as below.
primary index for any queries
CREATE PRIMARY INDEX primary-index ON Apps WITH {“defer_build”:true};
To improve the performance I’m removing the primary index and planning to go with the @N1qlSecondaryIndexed approach.
I request here to know, do we’ve any technique to design the indexes to decide which are all the fields need to be secondary indexed. Because I’ve kept getting this error during the Jenkins build and struggling to find the reason why the index is not coming online. But when I try to create the same index locally in the couchbase it’s getting created.
“{“msg”:“No index available on keyspace YS_Apps that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,“code”:4000}”
What I’ve created as secondary index now is below.’
CREATE INDEX idx_fundraising ON You_Apps(platform, _class) WHERE platform = ‘UO’ WITH {“defer_build”:true};
Can someone guide me on this, please? Please let me know if any other information is needed.
Do we need to have both primary as well as secondary indexes ? I confirm the secondary index has no errors and I was able to create it locally in the couchbase.
Since the secondary index is created with {“defer_build”: true}, did you issue the necessary BUILD INDEX statement to build the index and make sure it’s online? If you run the problematic query manually do you see the same error?
Thanks and Yes, defer build is placed next to the BUILD INDEX ON YS_Apps(idx_funding);.
Able to run the index query manually with no issues in the local couchbase instance.
Thanks for suggesting this Index Advisor but May I know do we’ve to write this query manually and ask for advice?
Since we’re using spring boot couchbase repositories all the queries were build behind the scene. I mean to ask, to approach the index advisor do we need to write the queries manually? But I tried to fetch dynamic queries generated by spring couchbase by enabling the spring property to ensure to cover all the fields in the where clause are indexed, yet I’ve no luck ! data.couchbase.repository.query=DEBUG
You might check system:prepareds, system:active_requests and/or system:completed_requests for the statement text that was/has been submitted to the query service.
I read on the shared link by you and tried a few like too SELECT *, meta().plan FROM system:active_requests;
But I couldn’t clearly understand what we can make use of in this context. Are you suggesting monitoring all the dynamic queries happening behind the screen by spring boot? Couchbase documents seem a bit hard for me to understand, though even multiple reads. Are you mean the above monitoring tools can be added along with INDEX queries? Just adding additional information, always adding the index query to the text file added to the Jenkins build after confirmed the index is getting created with no issues.
*SELECT , meta().plan FROM system:active_requests; - This can be added to the index file ?
The “statement” field is the text of the submitted statement that you can use with the index advisor. (Your “SELECT *, meta().plan FROM system:active_requests” to query active_requests is just a submitted statement the same as all others, hence also appears in the list.)
If your queries run quickly (say a few ms) then it may be difficult to catch one that is active - repeated runs of the select may help.
To catch fast statements it may be necessary to set a low threshold and query system:completed_requests instead; this:
sets a 5 millisecond threshold. (Return it to a higher threshold - default 1000 - again after gathering for a suitable period.)
WARNING: You will likely not want to run with a low threshold like this for too long; please see the notes on gathering this information in the documentation. ( Monitor Queries | Couchbase Docs )