Using Index not working correctly or am I using it wrong?

SQL++
, , ,
1

I have the following index-

CREATE INDEX `ix_events_active` ON `default`(`id`,`end_date`,`created_by`,`created_at`,`alert_id`,`name`,`child_counter`,`start_date`,
`zone_record`,`send_active`,`duration_type`,`creator_name`)
WHERE (((((`recipient_type` in [0, 3, 4])
and (`active_type` = 0))
and (`active_type` is not missing))
and (`parent_id` is missing))
and ((meta().`id`) like "event::%"))

And I am using the following query-

SELECT e.id, e.active_type, e.end_date, e.created_by, e.recipient_type, e.created_at, e.alert_id, e.name,
e.child_counter, e.start_date, e.zone_id_record, e.send_active, e.duration_type, e.creator_name AS user_name
FROM default e
WHERE e.active_type = 0
AND DATE_DIFF_STR(e.end_date, '2019-07-08T20:03:13', 'second') >= 0
AND e.created_by IN ['b37ddca31eae49085c96141ca6767ebd']
AND e.recipient_type IN [0, 3, 4] 
AND META(e).id LIKE 'event::%'
AND e.parent_id IS MISSING
AND e.active_type IS NOT MISSING
AND e.id IS NOT MISSING
ORDER BY e.name asc
LIMIT 10 OFFSET 0;

When I use the explain I can see that it is using intersect scan (ix_events_active and primary). Also the query is significantly slow(around 2 seconds). Even explicitly using the USE INDEX syntax doesn’t work, just slightly lesser time (1.3 seconds). I have tried different predicates in indexing and n1ql sections, but none seems to be working. Any solution please?

2

active_type = 0 implies active_type IS NOT MISSING. So you can drop that in index and query.
Drop primary index. If you can’t drop provide USE INDEX to avoid IntersectScan

The time taken is due to ORDER BY, Checkout https://blog.couchbase.com/create-right-index-get-right-performance/

CREATE INDEX `ix_events_active` ON `default`(`created_by`,`end_date`,`created_at`,`alert_id`,`name`,`child_counter`,`start_date`, `zone_record`,`send_active`,`duration_type`,`creator_name`, `id`)
WHERE `recipient_type` IN [0, 3, 4]
       AND `active_type` = 0
       AND `parent_id` IS MISSING
       AND meta().`id` LIKE "event::%";


SELECT e.id, e.active_type, e.end_date, e.created_by, e.recipient_type,
       e.created_at, e.alert_id, e.name, e.child_counter, e.start_date,
       e.zone_id_record, e.send_active, e.duration_type, e.creator_name AS user_name
FROM default e USE INDEX (ix_events_active)
WHERE e.active_type = 0
AND e.end_date >= "2019-07-08T20:03:13"
AND e.created_by IN ['b37ddca31eae49085c96141ca6767ebd']
AND e.recipient_type IN [0, 3, 4]
AND META(e).id LIKE 'event::%'
AND e.parent_id IS MISSING
AND e.id IS NOT MISSING
ORDER BY e.name asc
LIMIT 10 OFFSET 0;
3

Thanks for the advice. But I dropped active_type IS NOT MISSING clause. And explicitly used index by name as before. But still time is taking like before. I tried without the order by, still the same. I read few blog posts including what you provided, still couldn’t figure out what’s the thing causing this lag. Then I prefixed ‘EXPLAIN’ in my main code rather than in the couchbase console, and found out couchbase couldn’t recognise index because of parameters and when I used static values instead of parameters it was working perfect! Why this would happen? In that case I used explicit values, but there are some cases where I can’t. Any suggestion please?

4

You need to post the statement that uses parameters. Predicate as part of index condition can’t have dynamic parameters because prepare statement must work with different dynamic values, It may use different index.
I.e. query predicate that match index condition must be static or set adhoc=true (no prepare statements).
Also in case of IN clause right side is array is dynamic parameter it can’t push to indexer, must be processed in query .

1 Like
5

Here is the index prepared for a query which has dynamic parameterised value-

CREATE INDEX `ix_events_template` ON `default`(`created_by`,`name`,`id`,`created_at`,`alert_id`,`send_active`,`creator_name`,
shared_with)
WHERE `active_type` = 4
AND `recipient_type` IN [0, 3, 4]
AND `parent_id` IS MISSING
AND META().`id` LIKE "event::%"

And this is the query-

SELECT e.created_by, e.name, e.id, e.alert_id, e.send_active, e.creator_name AS user_name,
   e.active_type, e.recipient_type
FROM default e
USE INDEX (ix_events_template)
WHERE e.active_type=\$activeType
AND (e.created_by IN ['b37ddca31eae49085c96141ca6767ebd'])
AND e.recipient_type IN [0, 3, 4] 
AND META(e).id LIKE 'event::%'
AND e.parent_id IS MISSING
AND e.created_by IS NOT MISSING
ORDER BY e.created_at DESC  LIMIT

WHERE e.active_type=\$activeType this is the only predicate having parameter. Worth mentioning, I’m using couchbase server 4.5.1.

6

So, i have to skip using predicates having dynamic values in indexes, and I can use them only in the query. If that is the case, I understand now, silly me. Thanks for you valuable time!

7

Move active_type from index where clause to index key.

If index has WHERE active_type = 0 and query has active_type = $activeType . Query planning done during prepare time and at that time query parameters are not available. but Index has only entires of active_type = 0 and later if $activeType = 1 means no rows will return. This result in wrong result that is why index will not qualify (Index qualification done on static values)

1 Like
8

“Query planning done during prepare time and at that time query parameters are not available”—Now I’ve got it! Thanks for the explanation.

1 Like
9

we are still facing this issue in version 6.6.1 also. is there any fix for this issue in 7.x versions?

USE INDEX CLAUSE is not working properly in 7.1.3 as well.
have creaeted index like below and 1 or 2 queries picked this index remaining queries is still using other indexes and giving results in more than min 2-5 sec and max 40-50 sec.

CREATE INDEX dep_FUJI_AccMgmt_R23_KOK267025_classId_imei_iccid_CAID_BAID_DPID ON sessiondb_dep(imei,iccid,customerAccountId,billingAccountId,domainPackageId)
WHERE (classId = ‘421’) WITH {“num_replica”:1};

we have few queries like below,
select sessiondb_dep.* from sessiondb_dep where classId=“CRM_AUDIT” and sentToKafka= “flase”;

select sessiondb_dep.* from sessiondb_dep where classId= ‘421’ AND imei=“262021500639117”;

select sessiondb_dep.* from sessiondb_dep where classId= “421” AND iccid= “8981090000001639117”;

select sessiondb_dep.* from sessiondb_dep where classId=‘421’ AND customerAccountId= ‘074541d5b9a64794944926ae7e0dfed9’ AND domainPackageId=“MOBILE_SERVICE”;

select sessiondb_dep.* from sessiondb_dep where classId= ‘421’ AND billingAccountId=‘111111200004265’ AND domainPackageId=“MOBILE_SERVICE”;

what might be the reason why query engine is not picking newly created indexes in their plan ? use index also is not working at all.

could you help me on this problem.

10

CREATE INDEX ix1 ON sessiondb_dep( classId , imei,iccid,customerAccountId,billingAccountId,domainPackageId)

11

let me try in that way…

12

i have created this index "CREATE INDEX dep_FUJI_AccMgmt_R23_KOK267025_classId_imei_iccid_CAID_BAID_DPID ON sessiondb_dep( classId , imei,iccid,customerAccountId,billingAccountId,domainPackageId) WHERE (classId = ‘421’) WITH {“num_replica”:1}; "
second and third select queries are directly picking this index 4th and 5th are not picking this index , but i observed one thing after adding the classId as one of index field for that index now use index clause is picking dep_FUJI_AccMgmt_R23_KOK267025_classId_imei_iccid_CAID_BAID_DPID index.
getting confusion for me to understand this logics. do you have any idea/notes how this will work.

13

If query need to pick index leading key must be part of query predicate. So you have common classId on all queries so add that as leading key ( Avoid index WHERE unless that also same in all queiries)