Composite Queries & Indexes

[00:00:00]
>> Now, composite queries, they involve querying on more than one field. And so the thing about composite queries is that in Firestore if you wanna use composite queries, the way you have to create a composite index. And you can create them manually in the console, or you can just run your query, and then we'll spit out a link that says hey, we can't run this query until you have a composite index.

[00:00:28]
So click on this link and then it takes you out to the firebase console, and it will generate it for you. You just wanna say yes, this is the query I want, and it kinda pops up a little box like this. You say, create index, and it's because in this case, what I'd be doing is I would be querying on both category and costs.

[00:00:47]
So that's two fields. And this requires a composite index because, well, let's take a look at how indexing works. So a lot of databases behind the scenes, they handle this whole index list. Where what they do is they take a value and they associate it with the ID, and they sort it in a specific order.

[00:01:09]
So in this case, this is behind the scenes what a cost index would look like in Firestore. Is we have a cost of $0.54 and it's associated with this ID. And as you can see as we go down the list, the indexes are getting greater. So this is an ascending index.

[00:01:26]
And if I wanna get where costs is between $1 and $1.20, well, that's pretty easy because I have this nice sorted list. So it becomes a very fast operation to do these things. Well, what happens if I'm dealing with multiple indexes? So that's why querying is so fast and so easy with one field is because I can easily sort by one field.

[00:01:50]
But if I wanna do multiple, well, if I wanna see where category is equal to food, I have this ordered category index where I can say, okay, it's easy to find all IDs of food. And then for cost, I have the same ordered index. So it's easy to find that, but how do I know, do those have the category of food?

[00:02:13]
How do I put those together? And so that's why we create composite indexes. So composite indexes allow you to say, okay, where category is equal to food, and where cost is greater than $1 and less than 220. And behind the scenes what it does is it kinda this is a gross oversimplification of it.

[00:02:34]
But it concatenates or kinda combos these together where we have category and costs food and $1.05, food and 112, and so these are all sorted. That's what happens when you create the composite index, it creates a nice big sword for you. And then now your queries are very fast because there is a custom composite index for you to query off of.

[00:03:02]
Now big question that people get when they learn about composite indexes, they're like, well, why don't I have to manually create them? Why is it that if this is so fast, if this is good, why don't we just create one on every single document, and so that way we could composite index on everything?

[00:03:17]
Well, the math is not in our favor here. If you had a document of 20 fields, it would create this. I don't know what that number, was that says troops. It's a lot, the zeros tell the story. Someone who knows that you can let me know, it's a big amount.

[00:03:35]
So, and that's a lot to keep track of, that's a lot to sort, and that's gonna definitely not be performance. So we don't do it for every single one, that's why you specify the fields we do it on, and I think you can cover up to 200 composite indexes.

[00:03:53]
I will have to fact check myself on. But it's a fair amount that unless you're running a super high scale application, most people don't run into that 200 limit
>> Are they're no all in the queries?
>> There is all support that we're gonna get to in the very next section.

[00:04:13]
So yes, there is a type of or support where you can say get me all the expenses that are in the category of food or clothes for whatever reason you need to run that query. But yes, there is a way of doing that, and we're gonna do that in the very next exercise.

[00:04:33]
So all queries, yes, there's support for this.
>> Is there a way to order by date?
>> Yes, so how I specified order by cost in that section? You can just use that order by date and then specify whether descending or ascending. For simple queries, just if you're just squaring on date, all you have to do is that order by and you don't need to create a composite index for that.

[00:04:56]
But if you combine date with other fields, then you will need to create a composite index at that point, but you can still order by date.
>> Is it possible to get them by a random order?
>> Via random order, a lot of times your collections will be in random order.

[00:05:17]
Because documents are gonna be inserted into collections with generated IDs, and those IDs are not sequential. So in the real time database we have a certain construct called push IDs. And push IDs all have a bit of a timestamp element to them they're generated string, but they're done in a way where they're sortable.

[00:05:38]
So as you push new items on to real time databases and of collection, we kinda call them like nodes or locations. So as you push it on to a child node, each push sequentially pushes them on, so they're kinda in a certain order. With fire store, there is absolutely no order, those generated IDs do not follow a specific order.

[00:05:57]
So I can add ten records on one by one, and they can be in some mismatched order. And that's why we write an order query where you can order by cost sorted by this. So if you're using generated IDs, yes, you will just by default have a fairly random order.