{
  "@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":"<p>I replaced five indexes* on the <em>relationships</em> table with two**, improved query performance in at least one case, and cut the size of the database down by 11.4% (98MB).</p><p>Lessons (finally) learned:</p><ol><li>You can have too many indexes. At best, this makes the database larger. In at least one case, however, this caused the query planner to pick a less effective index, which resulted in worse performance.</li><li>Unless you understand the data well, it is hard to know what indexes you are going to need up front. For example, on the <em>relationships</em> table, it's clear in retrospect that an index on the <em>to_iri</em> column has better selectivity than an index on the <em>from_iri</em> column—and, in fact, no index is even necessary on the <em>from_iri</em> column. For reasons of symmetry, I created both when I created the table. <em>I'll go so far as to say, don't even create indexes until/unless you can analyze actual data.</em> (Aside: the SQLite3 function <a href=\"https://www.sqlite.org/lang_corefunc.html#likelihood\">likelihood</a> is an excellent way to hint about that data to the query planner.)</li><li>Ordering results using the automatically assigned, monotonically increasing <em>id</em> primary key behaves identically to ordering by something like <em>created_at</em>, so order by <em>id</em> and save yourself an index on <em>created_at</em>.</li></ol><p><a href=\"https://epiktistes.com/tags/ktistec\" class=\"hashtag\" rel=\"tag\">#ktistec</a> <a href=\"https://epiktistes.com/tags/sqlite\" class=\"hashtag\" rel=\"tag\">#sqlite</a> <a href=\"https://epiktistes.com/tags/optimization\" class=\"hashtag\" rel=\"tag\">#optimization</a></p><p>* The original five:</p><pre>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);</pre><p><br>* The final two:</p><pre>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);</pre>",
  "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":"<p><a href=\"https://epiktistes.com/actors/toddsundsted\" class=\"mention\" rel=\"tag\">@toddsundsted</a> Too many indexes kills insert performance as well.</p>",
  "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":"<p><a href=\"https://k.matthias.org/actors/relistan\" class=\"mention\" rel=\"tag\">@relistan</a> yes 💯 i forgot to mention that one!</p>",
  "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"
}


  ]
}