Hello,
Is it possible to create one index with two clauses “where” in the same bucket !
something like this : CREATE INDEX type ON default (type) where type = “rc-shop” and type = “rc-notification”;
query:
SELECT count(*)
FROM default as notif join default as shop
on keys “shop-” || TOSTRING(notif.fkidshop)
where ((notif.type = “rc-notification”) and (shop.type = “rc-shop”));
Thank’s
Nadjib
You want the following index.
CREATE INDEX idx_notif ON default (type) where type = "rc-notification";
Hi Geraldss,
I used it but the query tooks the same time !!!
is there another solution to optimaze my query ?
Can you post the output of EXPLAIN.
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “notifcity”,
“index_id”: “131661d17a81f63”,
“keyspace”: “ruecentrale-main”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“rc-notification”"
],
“Inclusion”: 3,
“Low”: [
"“rc-notification”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “notif”,
“keyspace”: “ruecentrale-main”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “shop”,
“keyspace”: “ruecentrale-main”,
“namespace”: “default”,
“on_keys”: “(“shop-” || to_string((notif.fkidshop)))”
},
{
"#operator": “Filter”,
“condition”: “(((shop.type) = “rc-shop”) and ((notif.type) = “rc-notification”))”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
"count()”
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: "count()”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “\r\nSELECT count(*)\r\nFROM ruecentrale-main as notif USE INDEX (notifcity USING GSI) join ruecentrale-main as shop \r\non keys “shop-” || TOSTRING(notif.fkidshop) \r\nwhere ((shop.type = “rc-shop”) and (notif.type = “rc-notification”))”
}
]
Ok, looks good. So what are you trying to count? If you are just counting notification, you do not need to JOIN the shops.
i’m traying to count shop with his notofications. (one shop can have a several notifications) 
it the reason why i join notifications to shop.
the query works good but the probleme is time … it tooks 6 sec 
i think that using secodry index will be better but for the moment i don’t find the magic recipe
If you can be more specific, we can take a look.
Hi @Jimmy_nadjib,
Make LEFT side of join covered by using following index by including on keys.
CREATE INDEX idx_notif ON default (type,fkidshop) where type = “rc-notification”;
If you can add additional predicate you can remove type from index keys to make it LEAN.
CREATE INDEX idx_notif ON default (fkidshop) where type = “rc-notification”;
SELECT count(*)
FROM default as notif join default as shop
on keys “shop-” || TOSTRING(notif.fkidshop)
where notif.fkidshop is not null AND (notif.type = “rc-notification”) and (shop.type = “rc-shop”);
Hi @vsr1,
I tried LEFTjoin with this index (CREATE INDEX idx_notif ON default (type,fkidshop) where type = “rc-notification”)
but not better 
the same with this index (CREATE INDEX idx_notif ON default (fkidshop) where type = “rc-notification”)
the query takes 5sec . 
Hi @geraldss
I need to join notification to their shop. and select (shop and notification) ““count(*) was just un exmple””
the result is correct but it tooks a lotoff time and the explain is :
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “notification”,
“index_id”: “b5bbe1a42b84828d”,
“keyspace”: “ruecentrale-main”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
““rc-notification””
],
“Inclusion”: 3,
“Low”: [
““rc-notification””
]
}
}
],
“using”: “gsi”
},
Can you post the full EXPLAIN. @vsr1 was suggesting that it should be a covering index. We need to confirm that in the EXPLAIN.
