Javascript: Searching indexeddb using multiple indexes

Question

I want to change from WebSql to Indexeddb. However, how would one do SQL queries like

SELECT * FROM customers WHERE ssn = '444-44-4444' and emal = 'bill@[email protected]'
SELECT * FROM customers WHERE ssn = '444-44-4444' and emal = 'bill@[email protected]' and age = 30
SELECT * FROM customers WHERE ssn = '444-44-4444' and emal = 'bill@[email protected]' and name = 'Bill'
etc

with IndexedDB ? For example, I noticed while reading the documentation of indexedDb, that all the examples only query one index at the time. So you can do

var index = objectStore.index("ssn");
index.get("444-44-4444").onsuccess = function(event) {
     alert("Name is " + event.target.result.name);
};

But I need to query multiple indexes at the same time!

I also found some interesting posts about compound indexes, but they only work if you query for all the fields in the compound index.

Puling

possible duplicate of In IndexedDB, is there a way to make a sorted compound query? – Outport

Answer 1

For your example, compound index still work, but requires two compound indexes

 objectStore.createIndex('ssn, email, age', ['ssn', 'email', 'age']); // corrected
 objectStore.createIndex('ssn, email, name', ['ssn', 'email', 'name'])

And query like this

 keyRange = IDBKeyRange.bound(
     ['444-44-4444', 'bill@[email protected]'],
     ['444-44-4444', 'bill@[email protected]', '']) 
 objectStore.index('ssn, email, age').get(keyRange)
 objectStore.index('ssn, email, age').get(['444-44-4444', 'bill@[email protected]', 30])
 objectStore.index('ssn, email, name').get(['444-44-4444', 'bill@[email protected]', 'Bill'])

Indexes can be arranged in any order, but it is most efficient if most specific come first.

Alternatively, you can also use key joining. Key joining requires four (single) indexes. Four indexes take less storage space and more general. For example, the following query require another compound index

SELECT * FROM customers WHERE ssn = '444-44-4444' and name = 'Bill' and age = 30

Key joining still work for that query.

Wait

thanks a lot!! However I still can't query using two indexes. I've created a [jsfiddle.net/jeanluca/sDeGz/](jsfiddle) to demonstrate the problem! I also had to change your parameter of objectStore.index into objectStore.index('ssn', 'email', 'age') otherwise I got a "DOM IDBDatabase Exception 8" exception. – Puling

sorry, I have corrected index to createIndex. no error should issue. but note, IE10 doesn't support compound index. – Wait

ok, thanks. So if I want to search on any combination of fields, I have to create an index for each combination ('ssn', 'ssn, name', 'ssn, email', ssn, name, email', etc) ? How expensive is an index ?, for example, if I have a huge objectStore with 20 indexes ? – Puling

If you need key range query, you have to index. 20 indexes is reasonable. However try to avoid exploding index problem developers.google.com/appengine/docs/python/datastore/… – Wait

Also not directly related, it might be worthy to note that when working with variables to make sure you pass the correct type. If in doubt, e.g. use parseInt() or similar. – Outstare

This might be the correct link for "key joining", if I'm not mistaken: dev.yathit.com/ydn-db/doc/query/key-joining.html – Preposterous

@KyawTun I'd love to see a short key joining example for the above example. – Preposterous