IndexedDB - boolean index

Question

Is it possible to create an index on a Boolean type field?

Lets say the schema of the records I want to store is:

{
  id:1,
  name:"Kris",
  _dirty:true
}

I created normal not unique index (onupgradeneeded):

...
store.createIndex("dirty","_dirty",{ unique: false })
...

The index is created, but it is empty! - In the index IndexedDB browser there are no records with Boolean values - only Strings, Numbers and Dates or even Arrays.

I am using Chrome 25 canary

I would like to find all records that have _dirty attribute set to true - do I have to modify _dirty to string or int then?

Fathead

Answer 1

Yes, boolean is not a valid key.

If you must, of course you can resolve to 1 and 0.

But it is for good reason. Indexing boolean value is not informative. In your above case, you can do table scan and filter on-the-fly, rather than index query.

Chemisorption

Correct answer but I disagree with its reasoning. Indexing a boolean would be informative when processing a large amount of records. Filtering on the fly is a complete waste of processing. And storing a key with a value of 0 instead of not storing the key at all is a waste of space. – Intramural

Index are useful only if they can be retrieved request key in O(log n) lookup time. In case of boolean index, lookup time is same whether it is indexed or not. – Chemisorption

I think I must respectfully disagree again. There is a significant performance difference between code that does store.openCursor(...).onsuccess = function() { if(this.result.value.someBoolean) { ... }} and store.index(...).openCursor(...).onsuccess = function() {} – Intramural

Respectfully, I must argue again. You are right that query on index.openCursor is faster than store.openCursor, but only 50% for boolean index value. It is not significant in asymptotic case. – Chemisorption

That reasoning requires the assumption that 50% of object have true and 50% false. In real world, you could have 100,000 with false value and only a few with value true. In that scenario, indexing a boolean really makes sense. – Toffic

Answer 2

The answer marked as checked is not entirely correct.

You cannot create an index on a property that contains values of the Boolean JavaScript type. That part of the other answer is correct. If you have an object like var obj = {isActive: true};, trying to create an index on obj.isActive will not work and the browser will report an error message.

However, you can easily simulate the desired result. indexedDB does not insert properties that are not present in an object into an index. Therefore, you can define a property to represent true, and not define the property to represent false. When the property exists, the object will appear in the index. When the property does not exist, the object will not appear in the index.

Example

For example, suppose you have an object store of 'obj' objects. Suppose you want to create a boolean-like index on the isActive property of these objects.

Start by creating an index on the isActive property. In the onupgradeneeded callback function, use store.createIndex('isActive','isActive');

To represent 'true' for an object, simply use obj.isActive = 1;. Then add or put the object into the object store. When you want to query for all objects where isActive is set, you simply use db.transaction('store').index('isActive').openCursor();.

To represent false, simply use delete obj.isActive; and then add or or put the object into the object store.

When you query for all objects where isActive is set, these objects that are missing the isActive property (because it was deleted or never set) will not appear when iterating with the cursor.

Voila, a boolean index.

Performance notes

Opening a cursor on an index like was done in the example used here will provide good performance. The difference in performance is not noticeable with small data, but it is extremely noticeable when storing a larger amount of objects. There is no need to adopt some third party library to accomplish 'boolean indices'. This is a mundane and simple feature you can do on your own. You should try to use the native functionality as much as possible.

Intramural

This is a great contribution. Have you done any cross-browser testing? – Tufa

Wouldn't the suggestion here only get you one half of the index? You can easily look up objects where isActive is set, but not those where isActive is not set. A real indexed boolean field would allow you to look up either objects where the filed is true or those where the field is false. – Toad

If you want to query by false, then use 1 and 0 in place of true and false, and open a range query on 0, e.g. openCursor(0) or openCursor(IDBKeyRange.only(0)) – Intramural

@Intramural Right. So I guess it's not so much that the accepted answer is not entirely correct, as that it's proposing a different (and more complete) simulation of boolean indexes than you are (using two arbitrary values of an indexable type to stand for true/false, rather than presence/absence of an indexable property). Damn, I was hoping I was missing something; this seemed a little nicer than remembering to always use my fake booleans everywhere, but I do need to be able to query the false/missing case as well as the true/present case. – Toad

@Intramural Although I, like you, disagree with the claim that boolean indexes wouldn't be useful. Why on earth did the standard writers leave them out? Ah well. – Toad

Answer 3

I've used 0 and 1 instead of boolean type.

Drying

Answer 4

Another alternative is to split the data into two separate object stores as commands_synced_to_server and commands_not_synced_to_server.

Then reading all commands_not_synced_to_server is the fastest as there is no lookup required from an index to the data and you are only reading the needed items.

The disadvantages are:

• It adds complexity.
• If you update the boolean field you need to delete the item from the true_values object store and insert it into the false_values object store.
• If you query and you do not know the boolean value, then you need to query two object stores. Since the query runs async, I would assume that there is not much performance difference compared to a single store. You could also duplicate all data to a third object store to optimize this case.

Stratiform

Answer 5

Boolean properties describe the exclusive state (Active/Inactive), 'On/Off', 'Enabled/Disabled', 'Yes/No'. You can use these value pairs instead of Boolean in JS data model for readability. Also this tactic allow to add other states ('NotSet', for situation if something was not configured in object, etc.)...

Lamentable