27

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@bill@company.com'
SELECT * FROM customers WHERE ssn = '444-44-4444' and emal = 'bill@bill@company.com' and age = 30
SELECT * FROM customers WHERE ssn = '444-44-4444' and emal = 'bill@bill@company.com' 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.

Community
  • 1
  • 1
Jeanluca Scaljeri
  • 26,343
  • 56
  • 205
  • 333
  • 1
    possible duplicate of [In IndexedDB, is there a way to make a sorted compound query?](http://stackoverflow.com/questions/12084177/in-indexeddb-is-there-a-way-to-make-a-sorted-compound-query) – Josh May 22 '14 at 19:15

1 Answers1

34

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@bill@company.com'],
     ['444-44-4444', 'bill@bill@company.com', '']) 
 objectStore.index('ssn, email, age').get(keyRange)
 objectStore.index('ssn, email, age').get(['444-44-4444', 'bill@bill@company.com', 30])
 objectStore.index('ssn, email, name').get(['444-44-4444', 'bill@bill@company.com', '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.

Kyaw Tun
  • 12,447
  • 10
  • 56
  • 83
  • thanks a lot!! However I still can't query using two indexes. I've created a [http://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. – Jeanluca Scaljeri May 12 '13 at 10:49
  • sorry, I have corrected `index` to `createIndex`. no error should issue. but note, IE10 doesn't support compound index. – Kyaw Tun May 13 '13 at 01:06
  • 1
    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 ? – Jeanluca Scaljeri May 13 '13 at 07:26
  • 1
    If you need key range query, you have to index. 20 indexes is reasonable. However try to avoid exploding index problem https://developers.google.com/appengine/docs/python/datastore/indexes#Index_Limits – Kyaw Tun May 13 '13 at 07:44
  • 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. – Chris Dec 17 '13 at 06:23
  • 3
    This might be the correct link for "key joining", if I'm not mistaken: http://dev.yathit.com/ydn-db/doc/query/key-joining.html – stackprotector Aug 31 '20 at 19:25
  • @KyawTun I'd love to see a short key joining example for the above example. – stackprotector Aug 31 '20 at 19:31