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 = "email@example.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 "firstname.lastname@example.org" 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
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?