N1QL Index performance

SQL++
1

Here is my problem, i have a collection of docs which keeps track of properties and they a grouped by track info etc. Since I don’t know what selection criteria my End user will use i have several query’s based on selection as there is no all for an IN clause. That said i have 2 do 2 queries as my grid needs to know how many Total Items there will be and then 2 second query gets me the actual data based on set limit and offset. All well so far…

so for example this is my query which will get me the count of all owners where either first or last name is like the value passed

select count(*) as count FROM Contacts AS f WHERE f._type="farm" and 
( lower(f.Owners.owner1FName) like lower('Smith') or lower(f.Owners.owner1LName) like lower('Smith') or lower(f.Owners.owner2FName) like lower( 'Smith') or lower(f.Owners.owner2LName) like lower( 'Smith'))

my index i build is like this

CREATE INDEXix5ONContacts((Owners.owner1FName),(Owners.owner1LName),(Owners.owner2FName),(Owners.owner2FLame)) WHERE (_type= "farm")

and here is my execution plan

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “4.311µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “7.58µs”,
“servTime”: “1.42546ms”
},
“privileges”: {
“List”: [
{
“Target”: “:Contacts”,
“Priv”: 12
}
]
},
“~child”: {
“#operator”: “CreateIndex”,
“#stats”: {
“#phaseSwitches”: 2,
“execTime”: “1.554µs”,
“servTime”: “7.077605522s”
},
“index”: “ix5”,
“keys”: [
{
“expr”: “(Owners.owner1FName)”
},
{
“expr”: “(Owners.owner1LName)”
},
{
“expr”: “(Owners.owner2FName)”
},
{
“expr”: “(Owners.owner2FLame)”
}
],
“keyspace”: “Contacts”,
“namespace”: “default”,
“using”: “default”,
“where”: “(_type = “farm”)”,
“#time_normal”: “00:07.0776”,
“#time_absolute”: 7.077607076
},
“#time_normal”: “00:00.0014”,
“#time_absolute”: 0.00143304
},
{
“#operator”: “Stream”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “12.007µs”,
“kernTime”: “7.079058613s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000012007
}
],
“~versions”: [
“2.0.0-N1QL”,
“6.0.0-1693-enterprise”
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000004311
}

So not sure why it takes that long to get a response which is around .5 sec. If i execute to get then the actual data it takes even almost 4 sec

2

Your plan is not query. It is CREATE INDEX plan.

You can try this

CREATE INDEX ix5 ON Contacts ( DISTINCT ARRAY LOWER(v) FOR v IN [Owners.owner1FName, Owners.owner1LName, Owners.owner2FName, Owners.owner2FLame] END) WHERE _type= "farm";

SELECT count(1) AS count
FROM Contacts AS f
WHERE f._type = "farm"
      AND ANY v IN [f.Owners.owner1FName, f.Owners.owner1LName, f.Owners.owner2FName, f.Owners.owner2FLame]
              SATISFIES LOWER(v) == LOWER('Smith') END;
3

Ok that did fix the count query but in process i lost the like Search function. And as i recall there is no Wildcard for IN.
I need to be able to to get partial matches so if user types “SMI” i want to get the count for all matches equal to
name like “SMI%”

4

After a bit playing around i found a way to make this happen based on your code

SATISFIES LOWER(v) like LOWER('Smi%') END