Sorting without order by

Hi @vsr1 I am stuck in performance of one query can you help?
So the scene here is, I have 1000’s of the document in a bucket and I want to search for one document which has queried timestamp in between the effective and expiry time. and if no match is found like there is no document which has queried timestamp in between the effective and expiry time, then I want to pick the nearest expired document. Below are the query and document.
Document - for example
{
“id”: “7f073bfb-c8d4-434b-925d-7520c0e2538e”,
“pageId”: “00000001”,
"effectiveTime": “1603090800000”,
** “expiryTime”: “1603263599999”**
}

SELECT * FROM bucket WHERE pageId=00000001 AND ((effectiveTime <= 1603177200000 AND 1603177200000<= expiryTime) OR (effectiveTime <= 1603177200000) ) ORDER BY expiryTime DESC LIMIT 1
this query takes - 44ms to run

SELECT * FROM bucket WHERE pageId=00000001 AND ((effectiveTime <= 1603177200000 AND 1603177200000<= expiryTime) OR (effectiveTime <= 1603177200000) ) DESC LIMIT 1
this query takes - 9ms to run

This query works absolutely correct, but when it is doing order by the expiry time, and then picking the nearest expired document. there it is taking 4 times more time what it takes without order By.
Without order by we are not sure, which document is returned, we just want the nearest expired document to return.

So any suggestions to achieve this result with a faster query.

if you need sort no way avoid it.

1 Like