N1Ql query performance with IN clause

SQL++
4

IN caluse has more than 1 element. So it must produce all possible values and order the results. This use case has improved in 6.0. You can try 6.0

In 5.1 Use covered query produce document keys and then fetch the documents

SELECT t.*
FROM bucket AS t
USE KEYS ( SELECT RAW d.id
           FROM ( ( SELECT META().id , type, orderType, orderDetails.code
                    FROM bucket
                    WHERE TRUE AND type = "ORDER" AND orderType = "X" AND orderDetails.code = "100"
                    ORDER BY type ASC, orderType ASC, orderDetails.code ASC LIMIT 200 )
                  UNION
                   ( SELECT META().id , type, orderType, orderDetails.code
                     FROM bucket
                     WHERE TRUE AND type = "ORDER" AND orderType = "X" AND orderDetails.code = "200"
                     ORDER BY type ASC, orderType ASC, orderDetails.code ASC LIMIT 200)
                ) AS d
           ORDER BY d.type ASC, d.orderType ASC, d.orderDetails.code ASC LIMIT 200 OFFSET 0)
ORDER BY t.type ASC, t.orderType ASC, t.orderDetails.code ASC;