Keys to Workplace Wellness Part 2 – Physical Wellness
December 11, 2019The WordPress Command Line Interface
January 8, 2020Couchbase – USE KEYS for performance gains
It is the holiday season, a great time of year for gains around the waist line, but also as the year ends, it’s a great time to look for performance gains within your code. Recently I was looking for ways to improve some of the Couchbase N1QL queries we use on a regular basis to improve performance.
Reading through some of the Couchbase Documentation, I discovered the USE KEYS syntax for N1QL queries.
There are a few USE operations you can read about at https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/hints.html, but today I want to look specifically at USE KEYS.
Typically in traditional SQL if you want to return something that is within a certain set of IDs you would do something like.
SELECT *
FROM table_name
WHERE id in [id1,id2,...]
And use the IN syntax to contain all the IDs you are looking to return. Knowing this I wrote a number of N1QL queries that used the same syntax.
SELECT *
FROM `travel-sample`
WHERE meta().id IN [id1,id2,...]
This worked pretty good and performance wasn’t bad on these queries, but it did require an index on the meta().id to work. Something like this.
CREATE INDEX `docID` ON `cdh`((meta().`id`));
I always thought this was a bit odd. But, this syntax was what I was used to from most SQL engines, so I didn’t think much of it. That is, until the end of the year and it became time for gains.
So, after reading the hints documentation on how to use the USE KEYS syntax, I redid those IN queries to be something like this
SELECT *
FROM `travel-sample`
USE KEYS [id1,id2,...]
The results are the same, but the above query eliminates the need for the docID index and performs around 50% faster. What is nice is you can combine the USE KEYS with any traditional indexes to find results even faster.
This was a quick win for some nice holiday gains in performance in frequently used N1QL queries. I hope you too can look around your code and find some gains to be had.
1 Comment
That’s awesome! It seems like there are always more performance improvements that can be made with Couchbase assuming you know what they are. I guess the trick is understanding their documentation inside and out 🙂