Hi
There has been discussion on this forum that DP 4.5 should improve ORDER BY performance in N1QL queries.
Using the index and query below it seems that ORDER BY still slows the query down quite a bit. Actually the value of LIMIT does not seem to matter much if ORDER BY is present. Array indexing introduced in DP4.5 seems to improve performance a great deal btw.
For example the commit below should have improved the ORDER BY performance according to another topic.
http://review.couchbase.org/#/c/57495/5
Is the commit in DP4.5 or are there other improvements coming? It’s hard to tell by looking at couchbase-query repository history since there’s no branch or tag for 4.5. Still the commit has been in master branch since early December so one could expect it to be included. I guess I could also build the latest version from master branch and test it.
Index:
CREATE INDEX ievents ON events (company, DISTINCT ARRAY r.`key` FOR r IN related END, time) WHERE docType = "EVENT";
Query:
SELECT time
FROM events
WHERE docType = "EVENT" AND company="company_9064af3e-bcf5-4c1f-b783-f723924856bf"
AND (ANY r IN related SATISFIES r.`key` = "item_07139ab1-5bac-48b8-8a92-373de9103edf" END)
ORDER BY time
LIMIT 100;
If ORDER BY is removed from the query the results are still ordered by time probably because of the index. It seems a little odd that ORDER BY still has a great impact. LIMIT value makes a huge difference in performance when there’s no ORDER BY.
Could you please check if there’s something wrong with the index or query and tell the current status of any planned improvements?
BR,
Mikko