| 165 | // 2. Deferred joins. https://planetscale.com/learn/courses/mysql-for-developers/examples/deferred-joins https://aaronfrancis.com/2022/efficient-pagination-using-deferred-joins |
| 166 | // They're ~2x faster than a normal limit+offset if including a FTS search criteria, but that's still not fast enough. Maybe FTS doesn't count as a covering index. |
| 167 | async function buildCache( |
| 168 | baseQuery: SelectQueryBuilder<DB, 'card' | 'note', Partial<unknown>>, |
| 169 | query: string, |
| 170 | sort?: Sort, |
| 171 | ) { |
| 172 | // const start = performance.now() |
| 173 | const cacheName = ('getCardsCache_' + |
| 174 | // lowTODO find a better way to name the cache table. Don't use crypto.subtle.digest - it's ~200ms which is absurd |
| 175 | md5(query + JSON.stringify(sort ?? 'noSort'))) as SearchCache |
| 176 | // const end = performance.now() |
| 177 | // console.info(`hash built in ${end - start} ms`) |
| 178 | const cacheExists = await ky |
| 179 | .selectFrom('sqlite_temp_master') |
| 180 | .where('name', '=', cacheName) |
| 181 | .select(ky.fn.count<SqliteCount>('name').as('c')) |
| 182 | .executeTakeFirstOrThrow() |
| 183 | .then((x) => x.c === 1) |
| 184 | if (!cacheExists) { |
| 185 | const { sql, parameters } = baseQuery.compile() |
| 186 | // console.log( |
| 187 | // 'PRAGMA temp_store', |
| 188 | // (await sql`PRAGMA temp_store;`.execute(db)).rows[0], |
| 189 | // ) |
| 190 | const start = performance.now() |
| 191 | await rd.exec( |
| 192 | `CREATE TEMP TABLE IF NOT EXISTS ${cacheName} AS ` + sql, |
| 193 | parameters as SQLiteCompatibleType[], |
| 194 | ) |
| 195 | const end = performance.now() |
| 196 | console.info(`Cache ${cacheName} for ${query} built in ${end - start} ms`) |
| 197 | } |
| 198 | return cacheName |
| 199 | } |
| 200 | |
| 201 | async function getCardsCount( |
| 202 | searchCache: SearchCache | null, |