Current Version
Couchbase Server Enterprise Edition 6.6.2 build 9588
i tried :
select count(*) from bucketname where docType=‘doctype1’ and appID=‘something’
loading for a long time , eventually times out
is there any quick way to get the count like N1QL query or anything else like curl request
curl http://hostname:8091/pools/default/buckets/beer-sample/ -u user:password | jq ‘.basicStats | {itemCount: .itemCount }’
i dont know how it works
Thanks in advance
It sounds like you have a large bucket and you don’t have an index to support your complete filter. If you create an index to support the complete filter you should get a faster response; something like:
CREATE INDEX ixt ON `bucketname`(`docType`,`appID`)
Without an index to support your query’s filtering it is having to fetch and evaluate the content of potentially many documents that don’t qualify.
Without the filters in the statement, the bucket statistics - like in your beer-sample illustration - are used internally to return a count quickly.
You can use EXPLAIN on your statement to see what access paths are in use.
If this is a statement you will not be running often and thus creating an index isn’t desired, you may find you’ll simply have to increase your request timeout to support the statement. (You haven’t stated which interface you’re using to issue the SQL++ statement.)
As you are using Enterprise Edition, you should be able to open a support case if you continue to experience issues.
(I will note that version 6 is out of support but that it is unlikely that a move to version 7 would affect these operations.)
HTH.
Hi thanks for the reply and suggestions,helped me for sure, i tried to get the count with curl request( http://hostname:8091/pools/default/buckets/beer-sample/ -u user:password | jq ‘.basicStats | {itemCount: .itemCount }’), it gave me a count of total items in the bucket, but could’nt get count for specific docType
And to the question of which interface im using to query - Couchbase Console UI
i will check the need for indexing in our bucket.
Awesome Thanks.
Yes, as expected. It is the simple “how many” (as opposed to the “how many are blue with stars” type) count which is a maintained statistic.
“docType” is just a “user” attribute of the documents - it could just as well be “colour” - hence no specific statistics are maintained for it, let alone statistics for it in combination with other attributes.
Creating an index on the combination of attributes you’re looking for effectively[1] (for the purposes of a COUNT statement such as yours) defines the additional statistics for the DBMS to calculate and maintain - your statement then just fetches them. (This is when the aggregate is able to be pushed down to the index.)
[1] It is a fringe benefit implicit in indexing. Indexes are still useful even when aggregates can’t be pushed down and have to be calculated on the fly, by reducing the number of documents that have to be evaluated.
In the UI, the request timeout (assuming your server doesn’t have a hard maximum limit imposed in its configuration that you’re hitting) is set under the gear icon in the upper right. It defaults to 10 minutes (600 seconds); you can set this to 0 (no limit) if you’re prepared to wait. (And as noted, the server’s configuration permits it.)
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.