JOin optimization

SQL++
1

Hi Team,

Below query to fetch 25 records is taking 9 min:
Select
a.acct_id,
a.channel,
m.sender.cur_iso3 as Originination,
m.prof_name as MoneyTransferFile
from dev1 a
inner Join dev1 m
On a.pri_prof.name=m.prof_key
Join dev1 q
on a.qqc_prof.name=q.prof_key
where
a.dtype=‘acct’
and m.dtype=‘mpriprof’
and q.dtype=‘qqcprofmap’
limit 25

Even i have created index but not .Looking forward for suggestions.

2 
CREATE INDEXX ixa1 ON dev1(pri_prof.name, qqc_prof.name, acct_id, channel) WHERE dtype = "acct";
CREATE INDEXX ixp1 ON dev1(prof_key, sender.cur_iso3, prof_name) WHERE dtype = "mpriprof";
CREATE INDEXX ixq1 ON dev1(prof_key) WHERE dtype = "qqcprofmap";

SELECT
      a.acct_id,
      a.channel,
      m.sender.cur_iso3 AS Originination,
      m.prof_name AS MoneyTransferFile
FROM dev1 AS a
INNER JOIN dev1 AS m ON a.pri_prof.name = m.prof_key
JOIN dev1 AS q ON a.qqc_prof.name = q.prof_key
WHERE a.dtype = "acct" AND m.dtype = "mpriprof" AND q.dtype = "qqcprofmap"
LIMIT 25;

Execute query in Query work bench console and then Click on Plan Text Tab. Check where time spent. The following article will details how to interpret the plan text.

3

Thankyou vsr1.

But sometime in where clause we have like condition as in below query then how to design index for that.
SELECT *, META().id FROM pricing_qa_1 as t WHERE dtype = ‘acct’ AND LOWER(t.acct_id) LIKE LOWER(’%A10060035%’) ORDER BY t.qqc_prof.name DESC LIMIT 24 OFFSET 0
Could you please suggest something.

Thanks
Ritu

4

Hi Vsr1,

IF suppose i want to do RIGHT Join.How to achieve same result in ablove query as RIght Join is not supported in Couchbase.

Thanks
RItu

5

Check out https://dzone.com/articles/a-couchbase-index-technique-for-like-predicates-wi

Right Join is supported with restrictions.

https://blog.couchbase.com/ansi-join-support-n1ql/