Indexes
If you want to look up objects by some of its properties and not just its key, or if you want to get a range of objects sorted by their properties, you need to create indexes.
An index is a list of property names, and for each property you also have to specify a type and sort order. When you store an object in a table with indexes, the object doesn't have to have properties that match those specified in the indexes, but if it does, it will from then on appear when you do matching index queries.
Inside an index, database objects are stored sorted by the values of the properties specified. For instance if an index over two properties looks like this:
- {Property:"Date",Type:Datetime, Order:Ascending}
- {Property:"Score",Type:Integer, Order:Descending}
Then the database objects will be sorted first by date ascending and then by score descending inside the index. This means that first comes the highest score for the oldest date up until the lowest score for the oldest date, then comes the highest score for the next oldest date, and so on until the last value in the index which will be the lowest score for the newest date.
Indexes can be added, changed and removed at any point in time, even if the table already contains data. If an index is added to a table that already has data, the index will be populated in the background, so it will take some times before all old objects appear in index queries. Objects that are stored in the table after the index was created will appear immediately.
It's also important to note that there are restrictions on indexes, there's a limit on the number of actual bytes an index contains, so depending on which types of properties you choose, there is a limit on the number of properties you can add. String properties on objects can be as long as you want, but when they are indexed, only the first 50 bytes of the string will go in the index. It is also impossible to add a property to the index that contains a nested object, a byte array, or a regular array. You can only index properties containing the simple datatypes.
Example
Let's say you're building a highscore-list. For each score you would then typically store the score itself, which player got the score, and when that player got the score. Here's an example of what the database objects in the table might look like:
To be able to get useful data out of this table, we construct the following two indexes:
- ByPlayer:
- {Property:"Player",Type:String, Order:Ascending}
- DateScore:
- {Property:"Date",Type:Date, Order:Ascending}
- {Property:"Score",Type:Integer, Order:Descending}
With these indexes, we can then write code that looks like this client-side:
And like this server-side:
Results
The results of the first query should be this:
The results of the second query should be this:
And finally, the results of the third query should be this: