SQLite – Partial Indexes
Any ideas why an index in SQLite would make a query slower? I have a 22 column table created with the city of Chicago crime data loaded (7.6M rows). There is a block column that has the block within the city where the crime occurred (about 58K unique block names). If I want to find all the blocks that start with a 1, I use &#x27;SELECT block FROM crimes WHERE block LIKE &#x27;1%&#x27;; which returns about 370K rows in about 1600 ms. If I then create an index on the block column, the same query takes about 1800 ms (about 200 milliseconds longer). I ran each query 5 times and the queries with the index are always slower.<p>I am much more familiar with Postgres and creating an index on the same table there always speeds things up quite a bit.