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.

possible duplicate of In IndexedDB, is there a way to make a sorted compound query? — Josh
– Josh, Commented May 22, 2014 at 19:15

Kyaw Tun · Accepted Answer · 2013-05-13 01:05:44Z

35

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.

edited May 13, 2013 at 1:05

answered May 12, 2013 at 0:57

Kyaw Tun

13.3k15 gold badges59 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jeanluca Scaljeri Over a year ago

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.

Kyaw Tun Over a year ago

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

Jeanluca Scaljeri Over a year ago

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 ?

Kyaw Tun Over a year ago

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/…

stackprotector Over a year ago

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

|

Collectives™ on Stack Overflow

Javascript: Searching indexeddb using multiple indexes

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related