I have created a GSI Index but I have no idea how to write a query where I can specify how to use this newly created index in a join using the keyword “USE INDEX [index_name] [index_type]”
The documentation only tells me how to use the "USE INDEX’ key word in a simple select statement. I’d like to know if this can be done using a JOIN NEXT CONTAINS etc…
Also if you could give me some example on how to do this.
Please post your CREATE INDEX statement, and then post your SELECT statement without the USE INDEX. We first need to understand what your are trying to do.
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;
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “beer_primary”,
“keyspace”: “beer-sample”,
“namespace”: “default”,
“using”: “gsi”
…
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%”;
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “beer_brewery_id”,
“index_id”: “b5217a9b9f96f44a”,
“keyspace”: “beer-sample”,
“namespace”: “default”,
…
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”.
hth,
-Prasad
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 Event.causeId=rule.ruleId
AND rule.pageGroup=‘homedefenders.com Quote Form’
END
END AND
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.