Query optimization approaches for filtering by multiple conditions? index vs filter

[Billiam]

I'm working on an app that loads in a large number of records (200k - 1m chat messages), for viewing and searching/filtering.

I'd like to improve search performance, particularly when a condition returns a large portion of the index.

My (slow but working) approach looks something like this (pseudocode):

  db.version(1).stores({
  messages: '++id, user, timestamp, ...other columns, *words',

const userKeys = db.messages.where('user').equals(myUser).primaryKeys()
const dateKeys = db.messages.where('timestamp').between(startOfRange, endOfRange).primaryKeys()
const matchingWordKeys = searchWords.map(word => {
  db.messages.where('words').equals(word).primaryKeys()
})
...
const matchingIds =  unionAll(await Promise.all(userKeys, dateKeys, etc, etc, ...matchingWordKeys))
const results = db.messages.where(':id').anyOf(matchingIds)

This is working fine, but some queries can take a long time.

For instance, one set of records has messages from 2016 to present, so if I filter for messages from 2017 to present, the majority of records will be returned by the dateKeys query above, making the dateKeys query itself slower than it needs to be. Depending on the conditions, many of these IDs may be filtered out in the unionAll afterward.

For some queries, it would be much faster to move this date query to a filter callback (perhaps only a few records match the user or word keys), or to invert the date range, and exclude those IDs from the matchingIds set instead.

My issue is that it's not obvious to me when it makes sense to do an additional indexed query like the above, vs filtering a smaller result set, or inverting the query.

My data is largely static, so I could preprocess it in some way to aid with later searches, but I'm not sure what approach to use here.

If I'm understanding correctly, Dexie 5.0 will be attempting to solve this issue (when to use an index vs filtering) for developers as well. What approach will it be taking for this kind of problem?