Very slow ORDER BY on 100K docs, even with index CB 4.5

SQL++
, ,
6

As you are using LEFT JOIN any predicate on right of LEFT JOIN causes null projected row throw away(makes it INNER JOIN).

You can do same thing in the USE KEYS like below, this eliminates fetch of unwanted documents vs elminate after fetch

USE KEYS (ARRAY tag FOR tag IN zstore.ItemTags WHEN tag IN [“TagId_a9f02e0d-d769-415a-a330-334b35d96c88”,“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”] END)
OR
USE KEYS ARRAY_INTERSECT(zstore.ItemTags ,[“TagId_a9f02e0d-d769-415a-a330-334b35d96c88”,“TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478”] )

SELECT zstore.*,
       ig[0].ItemsGroupName, ig[0].Discounts,
      (SELECT RAW c.CustomerName FROM zDB AS c USE KEYS zstore.CustomerId)[0] AS CustomerName,
      (SELECT t.TagId,t.TagName FROM zDB AS t USE KEYS ARRAY_INTERSECT(zstore.ItemTags, ["TagId_a9f02e0d-d769-415a-a330-334b35d96c88","TagId_dfcd2db7-7fcd-4257-ad06-a5f446ef8478"])) AS Tags,
FROM zStore AS zstore
LET ig = (SELECT i.ItemsGroupName, (SELECT d.DiscountId, d.Desc FROM zDB AS d USE KEYS i.Discounts) AS Discounts FROM zStore AS i USE KEYS ARRAY_INTERSECT(zstore.ItemsGroupId, ["ItemsGroupId_6249d04f-3cea-4bce-9b22-e6c690700cf7"]))
WHERE zstore.Type = "ItemId" AND zstore.DateCreated IS NOT MISSING AND LOWER(zstore.ItemName) LIKE "%itemname%"
ORDER BY zstore.DateCreated ASC
LIMIT 10 ;