Query is taking time

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;

Thanks For reply but I am getting below error.

[
{
“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;”
}
]

updated query. try again

I think you miss the query

query is not giving output

{
“results”: []
}

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;

First query is not giving any output.
and second query is keep on executing

If not giving output means there is no qualifying documents. You need to investigate the data.

but the original query is giving result.

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;

status requires prefix underscore. Drop and recreate index and run query from post#2

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;

Great Thanks for help its working fine now.
after recreating index and executing the query in 2nd post