Proper Indexing For ORDER BY

SQL++
1

Hi,

I have created the below INDEX
CREATE INDEX assets ON assets(status,type,created_on DESC)
For the following DOCUMENT

{
  "created_on": 1507299722,
  "modified_on": 1507299722,
  "eloc": "TTBT76",
  "type": "atlas",
  "source": 101,
  "asset_name": "43b174f6cbc818fb.jpg",
  "user_id": "38c345d166882c68d7029bb0960752c5",
  "status": 1,
  "pin_id": "4c032de3989b4b20015d3f109118518b"
}

When I run SELECT asset_name,created_on, eloc FROM assets WHERE status=0 AND type=‘review’ ORDER BY created_on DESC LIMIT 10;
The query takes 10ms which I expect from the query but when I run SELECT asset_name,created_on, eloc FROM assets WHERE status IS NOT MISSING AND type IS NOT MISSING ORDER BY created_on DESC LIMIT 10;
The query takes more than 2 seconds. Am I missing something and what is the correct way to achieve a faster result?
I always follow https://blog.couchbase.com/create-right-index-get-right-performance/ article for creating INDEXES.

2

Documents that have have no values for any of the fields listed in the index do not appear in in the index. This means that satisfying IS NOT MISSING clauses generally requires fetching the actual documents. This may account for the additional time in the second case. You can check what’s going on in the query plans using an EXPLAIN . If the documents are being fetched, you’ll see an operator named “Fetch”.

3

Your index and query will not use index order check out Appendix of https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

SELECT asset_name,created_on, eloc 
FROM assets WHERE created_on IS NOT MISSING 
ORDER BY created_on DESC LIMIT 10;
CREATE INDEX  `assets`  ON  `assets` ( `created_on`  DESC, asset_name, eloc );
1 Like
4

Thanks a lot @vsr1 you saved me.

5

Hi @vsr1

Below is my final index

CREATE INDEX assets ON assets(created_on DESC,status,type,eloc,source,asset_name)

Below Is my query based on index, I created

SELECT META(a).id,a.created_on,a.status,a.type,a.eloc,a.source,a.asset_name,u.name,u.user_name,
‘https://www.xyz.com/'||assetType||a.`asset_name` AS imageObject FROM assets AS a LEFT JOIN
users AS u ON KEYS a.user_id
LET assetType = CASE WHEN a.type=‘place’ THEN a.type||’/’ ELSE ‘explore/images/user_photos/’||CASE WHEN a.type=‘atlas’ THEN ‘pin’
ELSE a.type END||‘/original/’ END
WHERE a.created_on IS NOT MISSING AND a.status IS NOT MISSING AND a.type=‘atlas’ ORDER BY a.created_on DESC LIMIT 10 OFFSET 0

Total No of records present in the bucket is 1.3 million. The query takes more than 4 seconds but when I run
below quey

SELECT META(a).id,a.created_on,a.status,a.type,a.eloc,a.source,a.asset_name,u.name,u.user_name,
‘https://www.xyz.com/'||assetType||a.`asset_name` AS imageObject FROM assets AS a LEFT JOIN
users AS u ON KEYS a.user_id
LET assetType = CASE WHEN a.type=‘place’ THEN a.type||’/’ ELSE ‘explore/images/user_photos/’||CASE WHEN a.type=‘atlas’ THEN ‘pin’
ELSE a.type END||‘/original/’ END
WHERE a.created_on IS NOT MISSING AND a.status IS NOT MISSING ORDER BY a.created_on DESC LIMIT 10 OFFSET 0

It takes less than 30 ms. What exactly is going wrong?