Index not working for similar kind of query

SQL++
1

SELECT _id,name,url,tags,status,createdAt FROM content
WHERE (ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE “Cartoon Balloon Blow Up 01 %” END) END OR ANY array_element IN SUFFIXES(LOWER(name)) SATISFIES array_element
LIKE “Cartoon Balloon Blow Up 01 %” END) AND type = “IMAGE”
limit 10 offset 0

for this query the index created is like
CREATE INDEX UserAsset_image_name ON content((distinct (array array_element for array_element in suffixes(lower(name)) end))) WHERE (type = “IMAGE”)
This is working absolutely fine and returning result in 14ms .

I have created another query same as above but with type = “AUDIO”
SELECT _id,name,url,tags,status,createdAt FROM content
WHERE (ANY tag IN tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE “Cartoon Balloon Blow Up 01 %” END) END OR ANY array_element IN SUFFIXES(LOWER(name)) SATISFIES array_element
LIKE “Cartoon Balloon Blow Up 01 %” END) AND type = “AUDIO”
limit 10 offset 0

and the index for it is like
CREATE INDEX UserAsset_audio_name ON content((distinct (array array_element for array_element in suffixes(lower(name)) end))) WHERE (type = “AUDIO”)

For type=“AUDIO” the query is taking 2 minutes to execute and even not returning any result.

Do I have to change any thing for second query and index ?? so that it will return result with in miliseconds.

Thank You

2

You are indexing as lower( name ) and querying as LIKE “Cartoon Balloon Blow Up 01 %” . which has upper case letters so it will not match any items.

Try create single index and use for both queries.

CREATE INDEX UserAsset_audio_name ON content
   (type, DISTINCT ARRAY (DISTINCT ARRAY ae FOR ae IN SUFFIXES(LOWER(tag)) END) FOR tag IN ARRAY_APPEND(tags, name) END)
 WHERE type IN  ["IMAGE", "AUDIO"];

SELECT _id, name, url, tags, status, createdAt
FROM content
WHERE (ANY tag IN ARRAY_APPEND(tags, name) SATISFIES
              (ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae LIKE "cartoon balloon blow up 01 %" END) END)
       AND type = "AUDIO"
LIMIT 10
OFFSET 0;

SELECT _id, name, url, tags, status, createdAt
FROM content
WHERE (ANY tag IN ARRAY_APPEND(tags, name) SATISFIES
              (ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae LIKE "cartoon balloon blow up 01 %" END) END)
       AND type = "IMAGE"
LIMIT 10
OFFSET 0;

If you have are going to search with leading % then only use SUFFIX otherwise you don’t need it.