Below select query is taking 11 sec to execute can we optimize the below query.
SELECT * from lec WHERE _created WITHIN (SELECT MAX(_created) AS _created FROM lec As l2 GROUP BY _id) AND _id WITHIN (SELECT parentId as parentId FROM lec As l3 WHERE l3._status IN [‘COMPOSED’, ‘PUBLISHED’]) ORDER BY _created DESC;
CREATE INDEX ix_lstatus ON lec(_status,parentId);
CREATE INDEX ix_id ON lec(_id,_created);
SELECT m.* FROM lec AS l WHERE l._created IS NOT NULL AND l._id IN (SELECT RAW l3.parentId FROM lec AS l3 WHERE l3._status IN ["COMPOSED", "PUBLISHED"])
GROUP BY l._id
LETTING m = MAX([l._created, l])[1];
ORDER BY m._created DESC;
[
{
“code”: 4210,
“msg”: "Expression must be a group key or aggregate: l",
“query_from_user”: “SELECT l.* FROM lec AS l WHERE l._created IS NOT NULL AND l._id IN (SELECT RAW parentId FROM lec AS l3 WHERE l3.status IN [“COMPOSED”, “PUBLISHED”])\nGROUP BY l._id\nLETTING m = MAX([l._created, l])\nORDER BY l._created DESC;”
}
]
Break the query and check which condition evaluates false.
First query result will have second query _id
SELECT RAW l3.parentId FROM lec AS l3 WHERE l3._status IN ["COMPOSED", "PUBLISHED"]
SELECT l._id, m FROM lec AS l WHERE l._created IS NOT NULL AND l._id IS NOT NULL
GROUP BY l._id
LETTING m = MAX([l._created, l])[1];
ORDER BY m._created DESC;
SELECT * from lec WHERE _created WITHIN (SELECT MAX(_created) AS _created FROM lec As l2 GROUP BY _id) AND _id WITHIN (SELECT parentId as parentId FROM lec As l3 WHERE l3._status IN [‘COMPOSED’, ‘PUBLISHED’]) ORDER BY _created DESC;
The following query may be faster due to collect required document keys and then do fetch.
CREATE INDEX ix_lstatus ON lec(_status,parentId);
CREATE INDEX ix_id ON lec(_id,_created);
SELECT d.* FROM (SELECT RAW MAX([l._created, MEAT(l).id])[1]
FROM lec AS l
WHERE l._created IS NOT NULL
AND l._id IN (SELECT RAW l3.parentId
FROM lec AS l3
WHERE l3._status IN ["COMPOSED", "PUBLISHED"])
GROUP BY l._id) AS dkeys
JOIN lec AS AS d ON KEYS dkeys
ORDER BY d._created DESC;