IndexedDB Fuzzy Search

Question

Ok, first of all, sorry for my English.

I'm working in a web project that show suggests when I type something in the inputbox, but I want to use IndexedDB to improve the query speed in Firefox.

With WebSQL I have this sentence:

db.transaction(function (tx) {
  var SQL = 'SELECT "column1", 
                    "column2" 
             FROM "table"
             WHERE "column1" LIKE ?
             ORDER BY "sortcolumn" DESC
             LIMIT 6';

  tx.executeSql(SQL, [searchTerm + '%'], function(tx, rs) {
    // Process code here
  });
});

I want to do same thing with IndexedDB and I have this code:

db.transaction(['table'], 'readonly')
  .objectStore('table')
  .index('sortcolumn')
  .openCursor(null, 'prev')
  .onsuccess = function (e) {
    e || (e = event);
    var cursor = e.target.result;
    if (cursor) {
        if (cursor.value.column1.substr(0, searchTerm.length) == searchTerm) {
            // Process code here
        } else {
            cursor.continue();
        }
    }
};

But there's too slow and my code is buggy.. I want to know is there a better way to do this.

Thank for reply.

Answer 1

I finally found the solution to this problem.

The solution consist to bound a key range between the search term and the search term with a 'z' letter at the final. Example:

db.transaction(['table'], 'readonly')
  .objectStore('table')
  .openCursor(
    IDBKeyRange.bound(searchTerm, searchTerm + '\uffff'), // The important part, thank Velmont to point out
    'prev')
  .onsuccess = function (e) {
    e || (e = event);
    var cursor = e.target.result;
    if (cursor) {
      // console.log(cursor.value.column1 + ' = ' + cursor.value.column2);
      cursor.continue();
    }
  };

Because I need to order the result, so I defined a array before the transaction, then we call it when we loaded all data, like this:

var result = [];
db.transaction(['table'], 'readonly')
  .objectStore('table')
  .openCursor(
    IDBKeyRange.bound(searchTerm, searchTerm + '\uffff'), // The important part, thank Velmont to point out
    'prev')
  .onsuccess = function (e) {
    e || (e = event);
    var cursor = e.target.result;
    if (cursor) {
      result.push([cursor.value.column1, cursor.value.sortcolumn]);
      cursor.continue();
    } else {
      if (result.length) {
        result.sort(function (a, b) {
          return a[1] - b[2];
        });
      }

      // Process code here
    }
  };

Answer 2

I have been experimenting with IndexedDB and I have found it to be very slow, added to that the complexity of its api and I'm not sure its worth using at all.

It really depends on how much data you have, but potentially it'd be worth doing the searching in memory, and then you can just marshall and un-marshall the data out of some kind of storage, either indexedDB or the simpler localStorage.

Answer 3

I have lost ~2 hours on the same problem and I have found the real problem.

Here the solution :

• Replace IDBCursor.PREV by prev (it's awful but this is the solution)

IDBCursor.PREV is bugged at the moment on Chrome (26/02/2013)