@ingenthr
I think one issue with the post-n1ql-retrieval of the documents is that, when async, the order is no longer guaranteed to be the same from the ORDER BY in the N1QL query. When talking about hundreds of thousands of smallish (1kb) documents, I don’t want to keep it in memory to order them. I suppose I could make it all blocking but I think that would kill performance.
@geraldss
Couchbase Version: 4.1.0-5005 Enterprise Edition (build-5005), 3 node cluster
1.6 million docs using 6 out of 18gb in the bucket.
I’m hitting the right index based on the EXPLAIN plan (my index is on a string field and a numeric time field). About 30-40 fields in the document, not a huge document.
SELECT * FROM bwecl
AS ecl
WHERE serviceProviderUid
= ‘SP2200000000’ AND startTime
BETWEEN 1469836800000 AND 1469948720839 ORDER BY startTime
DESC;
VS
SELECT serviceProviderId
, releaseTime
, groupId
, callingAssertedNum
, primaryExtension
, type
, redirectingReason
, callAuthCode
, callingPresentationInd
, redirectingNumber
, detachedAnswerTime
, svcInvocNetworkType
, connectedName
, callLogType
, logId
, startTime
, primaryExtensionNumeric
, networkType
, basicCallType
, connectedNumberSource
, callId
, callingPresentationNumRestrict
, subscriberType
, docType
, networkTranslatedAddress
, svcInvocBasicCallType
, primaryPhoneNumber
, detachedTime
, userId
, svcInvocNetXlaAddress
, serviceProviderUid
, deleted
, redirectingName
, redirectingPresInd
, callingPresentationName
(you get the idea…)
BETWEEN 1469836800000 AND 1469948720839 ORDER BY startTime
DESC;
{ "serviceProviderId": "serviceProviderGoesHere", "releaseTime": 1469455695000, "groupId": "groupGoesHere", "callingAssertedNum": "+19726966650", "primaryExtension": "600", "type": "eclCallLog", "redirectingReason": "hunt-group", "callAuthCode": "4321", "callingPresentationInd": 1, "redirectingNumber": "+19726966670", "detachedAnswerTime": 1469455706000, "svcInvocNetworkType": 1, "connectedName": "user600@domain", "callLogType": 2, "logId": "++ecl++U22000000020", "startTime": 1469455653000, "primaryExtensionNumeric": 600, "networkType": 1, "basicCallType": 2, "connectedNumberSource": 4, "callId": "71e5618d-bafe-46d1-8e66-65fc25f91ce2", "callingPresentationNumRestrict": false, "subscriberType": 5, "docType": "eclCallLog", "networkTranslatedAddress": "+19726966600", "svcInvocBasicCallType": 2, "primaryPhoneNumber": "+19726966600", "detachedTime": 1469455703000, "userId": "user600@jberglund.rtx.broadsoft.com", "svcInvocNetXlaAddress": "+19726966600", "serviceProviderUid": "SP2200000000", "deleted": false, "redirectingName": "user670@domain", "redirectingPresInd": 1, "callingPresentationName": "user650@domain", "svcInvocDisposition": 19, "authAcctCode": "1234", "svcInvocCallCategory": 1, "callingGroupId": "North_as66", "callingPresentationNumSource": 4, "docTypeVersion": 1, "connectedPresentationInd": 1, "groupUid": "G2200000000", "userUid": "U2200000002", "callingPresentationNum": "+19726966650", "connectedNumber": "+19726966600" }
EDIT: forgot to mention, most situations will probably not have only two filters, our application supports searching on about 11 of them.