N1ql query is slow in join even indexes are created

SQL++
,
1

HI

Here is my query

SELECT
c.customer_id,
c.first_name,
c.last_name,
c.created_date,
c.status,
inv.referal_or_corp,
c.email,
c.address,
c.city,
c.zip_code,
c.state,
c.contact,
inv.store_id
from st_initialization_hq c
INNER JOIN st_data_bucket inv ON inv.cus_id = c.customer_id AND inv.type = ‘invoice’
WHERE c.type = ‘customer’ AND inv.date BETWEEN ‘2022-08-01’ AND ‘2022-08-31’

On couchbase index advisor suggested me these indexes

CREATE INDEX def_customer_1 ON st_initialization_hq(type,customer_id,city,state,status,contact,zip_code,created_date,email,address,last_name,first_name)

CREATE INDEX def_customer_2 ON st_data_bucket(type,date,cus_id,referal_or_corp,store_id)

CREATE INDEX def_customer_3 ON st_data_bucket(type,date,cus_id)

I have around 550 docs in db but query takes aroud 27s to execute which is too slow i think

Kindly Help Thanks

2

This is INNER JOIN try following (also provide HASH hint if needed)

SELECT c.customer_id, c.first_name, c.last_name, c.created_date, c.status,
       c.email, c.address, c.city, c.zip_code, c.state, c.contact,
       inv.referal_or_corp, inv.store_id
FROM st_data_bucket AS inv
JOIN st_initialization_hq AS c ON inv.cus_id = c.customer_id AND inv.type = "invoice"
WHERE c.type = "customer" AND inv.date BETWEEN "2022-08-01" AND "2022-08-31"

CREATE INDEX ix1 ON st_initialization_hq(customer_id,city,state,status,contact,zip_code,created_date,email,address,last_name,first_name) WHERE type = "customer";
CREATE INDEX ix2 ON st_data_bucket(date, cus_id, store_id,  referal_or_corp) WHERE type = "invoice";
3

Much better now its taking 169ms can you explain hash hint you mean this

SELECT c.customer_id, c.first_name, c.last_name, c.created_date, c.status,
       c.email, c.address, c.city, c.zip_code, c.state, c.contact,
       inv.referal_or_corp, inv.store_id
FROM st_data_bucket AS inv USE INDEX(ix1 USING GSI)
JOIN st_initialization_hq AS c USE INDEX(ix2 USING GSI) ON inv.cus_id = c.customer_id AND inv.type = "invoice"
WHERE c.type = "customer" AND inv.date BETWEEN "2022-08-01" AND "2022-08-31"
4

required EE and check example 9 ANSI JOIN Support in N1QL - The Couchbase Blog