speedup search

[azimut]

• try thread pool provided by wasm library
  If you use only a single connection, there is no difference between the two versions.
• see if I can return a list of promises, instead of a promise of a list, so I can maybe show search results as I receive them
• add pagination

[azimut]

Searching "Haskell" today ends up in 2.5Mb downloaded. Which includes in a LOT of non requested fields (content? description?). I guess it does scanning.

[azimut]

sqlite> SELECT entries.feedid,
       search.entriesid,
       search.title,
       entries.url,
       entries.datemillis
FROM search
JOIN entries ON search.entriesid=entries.id
WHERE search.content MATCH "Haskell"
ORDER BY entries.datemillis ASC;
QUERY PLAN
|--SCAN search VIRTUAL TABLE INDEX 0:M3
|--SEARCH entries USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
...

[azimut]

Dropping the description column from entries reduces a bit the size.

Before

Ocaml 514.05 kB / 594.38 kB transferred
Haskell 2.17 MB / 2.33 MB transferred

After

Ocaml 505.86 kB / 584.33 kB transferred
Haskell 2.07 MB / 2.22 MB transferred

[azimut]

Split entries.content into a different table should help.

Almost for sure it would help on the load time of "list episodes". Because right now is also fetching the whole records. But, I don't know how it will behave with the multiple index lookup would work.1

Another alternative would be add an index to entries.title. This would create a covering index2 which will limit the search to only the index. Which is what happens when I list the feeds and count the number of entries per feed.

sqlite> SELECT feeds.id, feeds.title, count(*)
            FROM feeds JOIN entries ON feeds.id=entries.feedid
        GROUP BY entries.feedid
          HAVING count(*) > 0;
QUERY PLAN
|--SCAN entries USING COVERING INDEX entriesindex
`--SEARCH feeds USING INTEGER PRIMARY KEY (rowid=?)

[azimut]

Tried adding 3+ indexes columns to the index of entries. Makes it download 1MB just to show the home page (aka getFeeds). 🙃
No idea why. I see .mp3 urls....oh, might be he gets the whole index. Meaning, it gets the 3 fields.

[azimut]

well, I think this is done for now. It still reads the content....but size seems to have decreased

along with a for sure speed up of display of entries of a feed

[azimut]

leaving open for 2nd task of list of promises

[azimut]

on pagination, using observer

• elm example https://ellie-app.com/mcrcwxkYS2Wa1
• elm example https://gist.github.com/Jonsey/03178640cd3124fef6828334e3e00b9c
• react example
  • https://github.com/azimut/reddit-gallery/blob/master/src/hooks/useInfinity.ts
  • https://github.com/azimut/reddit-gallery/blob/master/src/components/pages/SubReddit.tsx

[azimut]

ok, changing to use rowid in fts table instead of a separate column improved speed by a lot!

no longer I am receiving the whole search.content on search results

pagination might still be nice, might also show the number of results found while loading?