N1ql like query indexes

SQL++
1

I want to create indexes and search it using NIQL like query . i found one example on docs in which only one attribute is searching which works fine for me too but i dont know how to search multiple arritbutes using like query and i dont want to search this using FTS i am already using that too.

here is my indexes i created for both attributes

CREATE INDEX def_customer_first_name_suffixes ON stitchit_initialization_hq((distinct (array array_element for array_element in suffixes(lower(first_name)) end))) WHERE (type = “customer”)

CREATE INDEX def_customer_last_name_suffixes ON stitchit_initialization_hq((distinct (array array_element for array_element in suffixes(lower(last_name)) end))) WHERE (type = “customer”)

my query which is working fine but not using indexes . if i search with only one attribute it takes 10 ms but when searching with both first_name and last_name it take 2 seconds

select first_name,last_name from stitchit_initialization_hq
where
ANY array_element IN SUFFIXES(LOWER(first_name)) SATISFIES array_element LIKE ‘joe%’ END or
ANY array_element IN SUFFIXES(LOWER(last_name)) SATISFIES array_element LIKE ‘joe%’ END
and type=“customer”;

MY doc

{
“customer_id”: “006020000000”,
“first_name”: “Joe”,
“last_name”: “Rhode”,
“contact”: “”,
“gender”: “”,
“company_name”: “”,
“phone”: “34509837737”,
“birthday”: “”,
“address”: “”,
“complex”: “”,
“city”: “”,
“state”: “”,
“zip_code”: “”,
“email”: “asssssrr@gmail.com”,
“urgent_memo”: “”,
“customer_type”: “Regular”,
“hst_exempt”: “”,
“pst_exempt”: “”,
“exempt_1”: “”,
“exempt_2”: “”,
“key_tags”: “”,
“branch_id”: “60”,
“customer_class”: “Entry Level”,
“points_feature”: “yes”,
“enable_email”: “yes”,
“promotion”: “”,
“text_msg”: “yes”,
“male_neck”: 0,
“male_full_chest”: 0,
“male_front_chest_area”: 0,
“male_back_chest”: 0,
“male_full_shoulder”: 0,
“male_blazer_sleeve”: 0,
“male_bicep”: 0,
“male_wrist”: 0,
“male_trouser_outseam”: 0,
“male_thigh”: 0,
“male_knee”: 0,
“male_hips”: 0,
“male_half_back_lenght”: 0,
“female_neck”: 0,
“female_over_brust”: 0,
“female_brust”: 0,
“female_under_bust”: 0,
“female_waist”: 0,
“female_hips”: 0,
“female_neck_heel”: 0,
“female_arm_length”: 0,
“female_shoulder_seam”: 0,
“female_bicep”: 0,
“female_forearm”: 0,
“female_wrist”: 0,
“last_sale_old”: “43004”,
“status”: “Active”,
“cust_store_credit”: 0,
“customer_reviews”: ,
“created_date”: “2020-02-27”,
“updated_date”: “2020-02-27”,
“type”: “customer”
}

2 
CREATE INDEX ix1 ON default(DISTINCT ARRAY (DISTINCT SUFFIXES(LOWER(f))) FOR f IN [first_name,last_name] END) WHERE (type = "customer");
SELECT first_name, last_name
FROM default
WHERE type = "customer"
      AND ANY f IN [first_name,last_name] SATISFIES (ANY v IN SUFFIXES(LOWER(f))  SATISFIES v LIKE "joe" END) END;
3

Thank you so much :slight_smile: