CODEWITHSUNDEEP
selectindexingwhere-clausecouchbase
Gogo
Gogo

Reputation: 302

Couchbase order by index is very slow

I have created an index in s1_date but this query is still very slow (15 s) but when I remove "order by s1_date desc" it gets faster about (10 ms) This is the query:

          SELECT * FROM `bucket_1` WHERE  type = 'type1' AND (batch_number1 like '' OR batch_number2 like '' OR  batch_number2 like '' OR batch_number3 like '' OR batch_number4 like '' OR batch_number5 like '') order by s1_date desc limit 10

Upvotes: 0

Views: 74

Answers (1)

Johan Larson
Johan Larson

Reputation: 1890

This is a real nightmare of a query. An index on s1_date isn't going to help because the query engine has to apply the predicates type = 'type1' AND (batch_number1 like '' OR batch_number2 like '' OR batch_number2 like '' OR batch_number3 like '' OR batch_number4 like '' OR batch_number5 like '') first before doing any ordering, and it's the predicates that are going to be the majority of the work. In particular those ORs really mess things up for indexing, which means getting the query to run quickly is going to be a problem.

See if you can represent your data in some other manner so you don't have to use those ORs.

Upvotes: 1

Related Questions