Default ordering of resultset using secondary index

SQL++
1

Hi,

What would be the default sort order of a query if no “order by” statement is specified.
All fields used in “where” clause is indexed using GSI.

Currently we are getting consistent result and we assume that the result is ordered by the fields given in GSI.

Please let us know whether our assumption is correct or not.

2

There is no default order for query. If query does not have “ORDER BY”, results are not guaranteed any specific order.

If you need order you must specify the query ORDER BY. N1QL optimizer decides if sort required or can expose index order and avoid sort.

GSI index normally stores in ASC order (Unless you specified as DESC as part of index creation).

Checkout Rule#7 https://blog.couchbase.com/create-right-index-get-right-performance/

3

@vsr1 Thanks for the quick reply.

This is the index strcuture we have
CREATE INDEX index1 ON bucket(A,B,C) WHERE (A = “aa”);

Our current N1QL query :
select D,E,F from bucket where A=‘aa’ and B=‘bb’.

We require resultset with order by C.

Since A and B already exists in where clause, do we need to specify order by C explicitly?
(Reference : Rule #7)

4

Query never guarantee the results in specific order with out query ORDER BY.

You should use
select D,E,F from bucket where A=‘aa’ and B=‘bb’ ORDER BY C;

Still applies Rule #7 and query avoids sort in specific cases after analyzing (based on results from index did not alter the order).
Example: If query uses index1, no joins, no group by, no max_parallelism, query predicates A,B are equality predicate, …

You should do EXPLAIN and check Operator: Order at the end .

NOTE: If index1 is not online or query uses different index not specifying the query order by you may get results in different order. To avoid that you should specify ORDER BY if you expecting results in sorted order.

5

@vsr1,
When we apply ORDER BY C, query seems taking more time that query without ORDER statement.

But when we applied “ORDER BY A,B,C” , we are getting same performance as query without ORDER BY.

EXPLAIN output :

  1. Operator: Order shown when queried using “ORDER BY C”
  2. No Operator: Order shown with “ORDER BY A,B,C”

So we have concluded to use “ORDER BY A,B,C” since its giving better performance than “ORDER BY C”

Please share your feedback.

6

If you are using 4.6.x+ query detects there is equality predicate and “ORDER BY C” also give same performance. If older versions you need to specify ORDER BY A,B,C

7

@vsr1 Thanks for your support.