Index/Query Help

SQL++
1

Hi All,
I’m somewhat new to Couchbase, and are struggling with a query that is timing out after ten minutes. I suspect a large part of the issue is how we are storing our dates, that they need to be manipulated as part of the query…but changing it at this point is unfortunately not immediately feasible.

Any insight on indices (or combination of indices) or other tuning of the query that would be advisable is be greatly appreciated…I’ve experimented with different two different indexes individually to no avail as per below; please share any guidance:

SELECT number, numberType
FROM NoSQLDB
WHERE type = “com.model.CS” AND
STR_TO_MILLIS(SUBSTR(updateDate, 0, 26) || “:” || SUBSTR(updateDate, 26, 27))
BETWEEN STR_TO_MILLIS(“2020-09-01T17:00:10.599+00:00”) AND STR_TO_MILLIS(“2020-09-07T14:46:10.712+00:00”)

Some indices I’ve tried:

CREATE INDEX NoSQLDB-TEST ON NoSQLDB (updateDate, type,concat(substr(updateDate, 0, 26),":",substr(updateDate, 26, 27))) WHERE (type = “com.model.CS”) WITH { “num_replica”:1 }

CREATE INDEX NoSQLDB-TEST1 ON NoSQLDB(keyIdentifier,keyTypeCode,updateDate) WHERE (type = “com.model.CS”) WITH { “num_replica”:1 }

2 
CREATE INDEX NoSQLDBTEST ON NoSQLDB (concat(substr(updateDate, 0, 26),":",substr(updateDate, 26, 27)), updateDate,  `number`, numberType)
WHERE (type = "com.model.CS") WITH { "num_replica":1 };

https://index-advisor.couchbase.com/indexadvisor/

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/advise.html

3

Thank you, and thanks for the index advisor link, I wasn’t even aware that was available - I’ll give it a shot and circle back…fingers crossed.

4

This brought our query down from timing out after more than ten minutes on a large data to ~50 seconds on average; HUGE help, and even more-so on having the index advisor. Much appreciated!

5

Hi @aajordan ,

As you use “num_replica”:1 means you are using EE, you can explore partition index further speed up.

Also checkout https://blog.couchbase.com/ articles of interest