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.

Fong-Wan Chau · Accepted Answer · 2013-02-27T03:57:45.900

30

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
    }
  };

edited Feb 27 '13 at 03:57

answered Jan 22 '12 at 13:38

Fong-Wan Chau

2,259
4
26
42

1

Thanks alot! This cut it down from 3500 ms to 140 ms for me. – pimvdb Apr 08 '12 at 13:06
@pimvdb Good to know that you found it useful. I added some more example about sorting the result. – Fong-Wan Chau Apr 13 '12 at 18:28
7

It'd be better to use `\uffff` as your dagger rather than z. You won't get search results like "wikipædia" when searching for "wiki" if you use z... – odinho - Velmont Jul 18 '12 at 23:40
How do you use a wildcard at start of the search term? The following returns an error: IDBKeyRange.bound('\uffff' + searchTerm, searchTerm + '\uffff') – Anonymoose Aug 09 '13 at 07:59
2

@Hazaart Unfortunately, there are no way to use a wildcard at start of the search term. – Fong-Wan Chau Aug 09 '13 at 23:11

score 3 · Answer 2 · answered Aug 19 '11 at 09:09

3

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.

answered Aug 19 '11 at 09:09

Oliver Nightingale

1,805
1
17
22

Well, it seems that for Internet Explorer and Firefox I have to use a simple XMLHttpRequest, because XHR+PHP is even faster. It would be nice to see WebSQL be standard, because that way clients will use web applications with speed and be effective, also the server will save resources in a long term. – Fong-Wan Chau Aug 20 '11 at 04:36

score 2 · Answer 3 · edited Feb 26 '13 at 14:26

2

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)

edited Feb 26 '13 at 14:26

Hugo Dozois

8,147
12
54
58

answered Feb 26 '13 at 14:07

Arraxas

411
5
8

Well, the problem you had is not the same as I had. Your problem is caused by the changes on the new spec (updated on Feb 20, 2013): https://dvcs.w3.org/hg/IndexedDB/raw-file/tip/Overview.html . PS: I just updated my problem and my answer to match the new spec. – Fong-Wan Chau Feb 27 '13 at 04:00

IndexedDB Fuzzy Search

3 Answers3

Linked