Mehdi Akiki

Posts tagged "indexing"

  • Published on

    Investigation · Part 8 of 10 · Inside a database index

    Partial and Expression Indexes: Index Only What You Query

    A partial index of the 2% active users took 138 pages instead of 9,096 and answered as fast. But SQLite uses it only when the query says the condition in the same way, or binds the same value, and an index on json_extract() is ignored by the ->> operator, which returns the same value.
  • Published on

    Investigation · Part 6 of 10 · Inside a database index

    When the Database Ignores Your Index

    An index exists, and the plan still says SCAN. I tested six cases on SQLite: a function on the column, LIKE, a leading wildcard, OR, a join between a TEXT and an INTEGER column, and a value that matches 93% of the rows. In the last one, ignoring the index is the right choice.
  • Published on

    Investigation · Part 5 of 10 · Inside a database index

    A Covering Index Never Touches the Table

    When an index has every column a query needs, SQLite never opens the table. For one row this saves almost nothing. For 67,245 rows it was 98 ms against 21 ms with a warm cache, and 875 ms against 24 ms with a cold one.
  • Published on

    Investigation · Part 4 of 10 · Inside a database index

    Why Column Order Matters in a Composite Index

    Two indexes with the same two columns in a different order. For the same query, one reads 1,885 entries and the other 27,243. I measured why on a SQLite table of one million rows, and found that the leftmost-column rule has an exception called skip-scan.
  • Published on

    Investigation · Part 1 of 10 · Inside a database index

    An Index Is a Second Copy of Your Data

    I added one index to a SQLite table of one million users and read the file page by page. The index is a second sorted copy of the emails, each one with the rowid of its row, and when I broke one entry the database returned a row that does not exist.