Query or Search — Why Not Both?
Day 2 of attending the Couchbase Connect event and, as promised in my last report, I dive a little more into some of the N1QL query updates that impact full-text searching.
As an aside, I used to work at Couchbase and was the Product Manager that launched the Full-Text Search product — so it is near to my heart even now. That is why I’m doing my “Simplified Text Search” talk on Friday at the same event. More on that at the end of this article.
Full-text search (FTS aka Search) is a topic of its own but all you need to know for this article is that it is a separate service or set of capabilities from the N1QL SQL-like querying service of Couchbase. Similar to relational databases, N1QL leverages a set of indexes known as Global Secondary Indexing (GSI).
The Couchbase Search service has its own set of indexes and allows more flexible searching, with powerful language awareness, for text within fields or objects in JSON documents.
Binh Le’s talk Flex Index — Leverage Search With Standard SQL Predicate Without Query Limitation dives into the N1QL features that allow a query to use either GSI or FTS indexes depending on need. This is a cool new feature and there are two ways it is accessible.
Slides below are from Binh’s talk — please see the full talk for a much deeper dive and some live examples.
N1QL with Search Predicate Function
The first way to leverage an FTS index from within a N1QL search is by adding a SEARCH() predicate to the WHERE clause. This illustration shows the various ways to query across Couchbase services — providing a single entry point depending on the use case.
#3 in the above graphic shows the N1QL call to the SEARCH function:
WHERE … SEARCH(indexName, ‘text to search for’)
This will limit results to matches between all predicates in the WHERE clause and documents that have a match for the search. This uses the most basic FTS text query capabilities but is intentionally simple.
N1QL with Index Hint
The alternative approach is shown in Binh’s slide at the bottom right. Incidentally, this slide is a great summary of approaches across other database and search products.
In the case of index hinting, a simple statement in the FROM clause is all that is needed to leverage existing FTS indexes:
FROM crm USE INDEX(USING FTS)
It could also explicitly use (USING GSI) but that's the default I believe.
By using this hint the N1QL query interpreter will check if the columns used in the WHERE clause (e.g. title and type) are indexed by FTS. If so, they will be used instead of any GSI-related indexes — without any other input required.
Note that the fields in the SELECT clause will be automatically drawn from the source documents and returned to the user without having to make a second call from your application.
Prefix searching with LIKE predicates, date ranges, numeric ranges, and more are supported by this approach and converted into powerful FTS queries behind the scenes. See Binh’s comprehensive blog post for more details.
How will you use this?
The workflow is simple: index relevant fields for text search, then add the hint to your existing queries. This is great for prototyping and testing with FTS. You can add a tiny clause to your queries and not have to change applications in a substantial way.
Then, if you realize that you want to use text searching instead of N1QL, you can get an idea of how responsive it will be. The goal of FTS is to be faster and to give you a rapid set of relevant and scored matches.
Alternatively, if you are already using FTS to power your applications, you can try taking advantage of N1QL queries instead so you do not have to make a separate call to retrieve field values from matching documents.
Why does this matter?
Every enterprise struggles with maintaining a growing set of increasingly complex systems. I have always been an advocate for a modular systems approach that allows system designers to swap in/out different components as needed. However, that is really only ideal during the testing and development phases. For long term planning, it is highly beneficial to standardize on a core set of capabilities and having integrated as tightly as possible.
Couchbase aims to deliver an overall platform for all key-value and document needs — including querying, text searching, analytics, eventing, etc. all on a distributed, high-performance platform. This is more than just marketing speak :). This consolidation of capabilities allows administrators and developers to become experts in a single platform instead of having to learn several.
If you could collapse several system components into a unified experience I believe you’d see it impact your TCO in ways you wouldn’t necessarily expect.
I think that’s important and why I’m bullish on this type of N1QL / Search integration — bravo for simplicity!
I hope you’ll join my talk tomorrow (day 3 of the event) at 10:00 PDT — Simplified Text Search — Methods and Architecture where I give a gentle introduction to full-text searching concepts and help you understand the overall architecture of the service. Register here.
Thanks for reading!
