Currently, the search for "house" takes a considerable amount of time to complete (longer than five seconds).
Research improving the query speed.
Notably from what I can see, the two areas of impact are the subquery and the loop of queries (senses, glosses, pos).
SELECT Entry.id, Entry.is_common AS isCommon, Entry.kanji, Entry.reading
FROM Entry
WHERE Entry.id IN (
SELECT sub.id
FROM Entry AS sub
LEFT JOIN Sense
ON sub.id = Sense.entry_id
LEFT JOIN Gloss
ON Gloss.sense_id = Sense.id
WHERE Gloss.value LIKE '%'||:keyword||'%'
);
val entries = database.entryQueries.selectEntriesByGloss(query, ::Mapper).executeAsList()
...
entries.map { entry ->
...
// here we query at least once for each entry found, then twice for each sense
// "house" returns over 1000 entries, so that's at least 3000 queries
val senses = database.senseQueries.selectSenses(entry.id).executeAsList().map { senseId ->
val pos = database.sensePosTagQueries.selectPosWhereSenseIdEquals(senseId).executeAsList()
val glosses = database.glossQueries.selectGlossWhereSenseIdEquals(senseId).executeAsList()
Sense(glosses, pos)
}
...
}
Currently, the search for "house" takes a considerable amount of time to complete (longer than five seconds). Research improving the query speed.
Notably from what I can see, the two areas of impact are the subquery and the loop of queries (senses, glosses, pos).