You can try USE INDEX with JOIN like this (using the beer-sample data set shipped with couchbase server).
CREATE INDEX beer_brewery_id ON `beer-sample`(brewery_id) WHERE (type = “beer”);
CREATE PRIMARY INDEX beer_primary ON `beer-sample`;
explain select * from `beer-sample` beer USE INDEX (beer_primary) JOIN `beer-sample` brewery on keys beer.brewery_id LIMIT 4;
explain select * from `beer-sample` beer USE INDEX (beer_brewery_id) JOIN `beer-sample` brewery on keys beer.brewery_id
where beer.type=“beer” and brewery.type = “brewery” and beer.brewery_id LIKE “%cafe%”;
Note that the SELECT must follow these two important rules (loosely described) to use the index:
a) must have a predicate/condition in the where-clause that specifies the leading index keys used in the create index. In this example (2) above, it uses beer.brewery_id LIKE “%cafe%”, where brewery_id is the index-key in the index-definition of the index beer-brewery-id.
b) if the index is a partial index, then the SELECT/where-clause must also have matching predicates. Example (2) above has beer.type=“beer”.
PS: provide your N1QL statements, as Gerald mentioned, in case you hit issues…
SELECT * FROM Session_Bucket Session
NEST Events_Bucket aryEvents ON KEYS Session.aryEvents
LEFT NEST PageRule_Bucket aryPageRules ON KEYS aryEvents[*].causeId
WHERE Session.companyId = " " AND
NOT ANY Event IN aryEvents SATISFIES
ANY rule IN aryPageRules SATISFIES Event.result='form-completed’
AND rule.pageGroup='homedefenders.com Quote Form’
ANY rule IN aryPageRules SATISFIES rule.pageGroup='unknowns’
END AND Session.aryBreStatus.flagQ1 IS MISSING AND Session.lastEventTime < (NOW_MILLIS() - 300000)
and how the index was created.
CREATE INDEX idxCompanyId ON Session_Bucket(companyId) USING GSI;
will n1ql default this index if I do not specify it in the query?
Ok, great. Now if you compute NOW_MILLIS() - 30000 on the client side, and pass that as the value of $time, you should see better performance. Is there also a minimum time you should add to your query? Right now, it gets all events from the beginning of time, up to five minutes ago.