You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
during working on showing reactions in summaries i noticed that there is no sql-index over any reactions column - where we were adding index otherwise on even smaller tables - and reactions table can become quite big, it is only currently small as it is a new feature :)
also, the reaction table is queried often - at least for every message and every chatlist item displayed.
reactions use the following statements that need a full scan currently:
SELECT contact_id, reaction FROM reactions WHERE msg_id=?
SELECT reaction FROM reactions WHERE msg_id=? AND contact_id=?
SELECT reaction FROM reactions WHERE msg_id=? AND contact_id=?
DELETE FROM reactions WHERE msg_id=? AND contact_id=?
all of them could benefit from an index over msg_id - combined index is probably over the top as the most often called query is the first one.
wondering if the index was left out on purpose? cc @iequidoo@link2xt - otherwise, we should add one with CREATE INDEX reactions_index1 ON reactions(msg_id)
EDIT: or does the FOREIGN KEY(msg_id) imply an index? my source of truth is the sqlite3browser, maybe i've also overseen sth
The text was updated successfully, but these errors were encountered:
during working on showing reactions in summaries i noticed that there is no sql-index over any reactions column - where we were adding index otherwise on even smaller tables - and reactions table can become quite big, it is only currently small as it is a new feature :)
also, the reaction table is queried often - at least for every message and every chatlist item displayed.
reactions use the following statements that need a full scan currently:
all of them could benefit from an index over
msg_id
- combined index is probably over the top as the most often called query is the first one.wondering if the index was left out on purpose? cc @iequidoo @link2xt - otherwise, we should add one with
CREATE INDEX reactions_index1 ON reactions(msg_id)
EDIT: or does the
FOREIGN KEY(msg_id)
imply an index? my source of truth is the sqlite3browser, maybe i've also overseen sthThe text was updated successfully, but these errors were encountered: