{ "@context":"https://www.w3.org/ns/activitystreams", "type":"Collection", "id":"https://epiktistes.com/objects/mLbDJyZKkJQ/thread", "items":[ { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2024-01-21T19:49:04.300Z", "attributedTo":"https://epiktistes.com/actors/toddsundsted", "replies":"https://epiktistes.com/objects/h4jE5pNpqt0/replies", "to":["https://www.w3.org/ns/activitystreams#Public"], "cc":["https://epiktistes.com/actors/toddsundsted/followers"], "content":"
I replaced five indexes* on the relationships table with two**, improved query performance in at least one case, and cut the size of the database down by 11.4% (98MB).
Lessons (finally) learned:
#ktistec #sqlite #optimization
* The original five:
CREATE INDEX idx_relationships_type_from_iri_created_at\r\n ON relationships (type ASC, from_iri ASC, created_at DESC);\r\nCREATE INDEX idx_relationships_from_iri_created_at_type\r\n ON relationships (from_iri ASC, created_at DESC, type ASC);\r\nCREATE INDEX idx_relationships_type_to_iri\r\n ON relationships (type ASC, to_iri ASC);\r\nCREATE INDEX idx_relationships_to_iri_type\r\n ON relationships (to_iri ASC, type ASC);\r\nCREATE INDEX idx_relationships_type_id\r\n ON relationships (type ASC, id ASC);
* The final two:
CREATE INDEX idx_relationships_type\r\n ON relationships (type ASC);\r\nCREATE INDEX idx_relationships_to_iri\r\n ON relationships (to_iri ASC);", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Hashtag","name":"#ktistec","href":"https://epiktistes.com/tags/ktistec"}, {"type":"Hashtag","name":"#sqlite","href":"https://epiktistes.com/tags/sqlite"}, {"type":"Hashtag","name":"#optimization","href":"https://epiktistes.com/tags/optimization"} ], "type":"Note", "id":"https://epiktistes.com/objects/mLbDJyZKkJQ" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2024-01-22T19:56:04.065Z", "attributedTo":"https://k.matthias.org/actors/relistan", "inReplyTo":"https://epiktistes.com/objects/mLbDJyZKkJQ", "to":["https://www.w3.org/ns/activitystreams#Public","https://epiktistes.com/actors/toddsundsted"], "cc":["https://k.matthias.org/actors/relistan/followers"], "content":"
@toddsundsted Too many indexes kills insert performance as well.
", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Mention","name":"@toddsundsted@epiktistes.com","href":"https://epiktistes.com/actors/toddsundsted"} ], "type":"Note", "id":"https://k.matthias.org/objects/uQm0-AEisuA" } , { "@context":[ "https://www.w3.org/ns/activitystreams", {"Hashtag":"as:Hashtag"} ], "published":"2024-01-22T21:14:07.055Z", "attributedTo":"https://epiktistes.com/actors/toddsundsted", "inReplyTo":"https://k.matthias.org/objects/uQm0-AEisuA", "replies":"https://epiktistes.com/objects/yhaTMnySE1s/replies", "to":["https://www.w3.org/ns/activitystreams#Public","https://k.matthias.org/actors/relistan"], "cc":["https://epiktistes.com/actors/toddsundsted/followers"], "content":"@relistan yes 💯 i forgot to mention that one!
", "mediaType":"text/html", "attachment":[], "tag":[ {"type":"Mention","name":"@relistan@k.matthias.org","href":"https://k.matthias.org/actors/relistan"} ], "type":"Note", "id":"https://epiktistes.com/objects/yhaTMnySE1s" } ] }