Query is taking time

n1ql

#1

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;


#2
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;

#3

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


#4

updated query. try again


#5

I think you miss the query


#6

query is not giving output

{
“results”: []
}


#7

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;

#8

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


#9

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


#10

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;


#11

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


#12

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;

#13

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