How to make a view equivalent to the following SQL?


#1

N1QL in its current state isn’t fast enough (by several orders of magnitude) to use even for development right now. I understand that it is under development and performance hasn’t been a concern yet.

However, I’d like to use Couchbase now and I’m trying my hardest to select it as the database for my project. Since N1QL isn’t ready I must use views. I need performance comparable to MySQL for my queries.

I know SQL so I’ll state one of my needed queries as a SQL query. It is a pretty basic SQL query, trivial for the average SQL programmer. No joins or anything very complex:

theDocumentID As id, UIDValidity, UID, * FROM messages AS message WHERE message.type = "message_header" AND message.account = "neatcode@yahoo.com" ORDER BY UIDValidity DESC, UID DESC LIMIT 40

This has to execute fast (<1 sec preferably) and be on the most recent data. I need to be able to change the "neatcode@yahoo.com" and possibly “message_header” values to different values on EACH query I make. This is trivial with SQL, how hard is it with Couchbase? Is it doable?

How do I do this basic SQL query using a view?, and what are the parameters I need to give to filter by message_type and account which are dynamic and specified application-side, and what parameters do I need to sort by descending, twice (not alphabetical string descending, but numerical descending).

So far from my reading of documentation and blogs, etc, this relevantly trivial SQL seems like a really tough thing to do in Couchbase, which means I may have to drop Couchbase for use in my current project.

I’m hoping a full example of how to solve this problem and its various aspects will give me a clue as to how to handle future queries in Couchbase, and I imagine it would help others a great deal if they’re considering Couchbase.

I need this query to go over 25,000 items right now (will be hundreds of millions in production and more), and I need the data to be current as of the last write, and I need the performance to be similar to MySQL or better (easily <1-2 sec).

How would you do this SQL query, the “Couchbase” way?


#2

the exact syntax would depend on your attribute names in JSON so you may need to do some edits here but I’ll take a stab. There are a few assumptions:

  • I assume you will have many more types of data beyond “messages” in the database (or in sql terms many tables beyond a single table). You will filter on the doc type=“messages” so you can get a dedicated index for doc.type = message.
  • I assume this is the only query, if you have other queries, you may create an index that does not emit all fields (or create a fully covering index) and instead, can look up based on keys you get from the view.

your view would look something like this:
function (doc, meta) {
if (doc.type="messages"
emit([doc.messege_type,doc.message_account],[doc. all the fields you want to emit]);
}

in your query you could specify the key to be [“messege_header”,“neatcode@yahoo.com”].
thanks


#3

@cihangirb Thanks for your example.

How to do the full query… the “ORDER BY” part (numerically)?:

ORDER BY UIDValidity DESC, UID DESC