Is it faster to read from fixed-width columns in SQLite?

Question

Would it generally be faster to read from a table where there are no varchar or other variable length data stored? In MySQL, this is faster because it can calculate exactly where a row will be stored on the disk.

Monophonic

Answer 1

This question is not meaningful in the context of SQLite as it supports only a single TEXT field type. The distinction of "fixed-width" vs. "variable-length" doesn't exist here.

While SQLite will let you define a field as having a certain type, all this does is (at most) set that field's preference for the type to use when storing ambiguous data (e.g., whether "3" will be stored as INTEGER, REAL, or TEXT). You can still store any kind of data in any SQLite field regardless of its type.

Specifically relating to CHAR vs. VARCHAR, http://www.sqlite.org/datatype3.html tells us:

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

Relay

But I could also remove all text/varchar/char columns from a table. It would then be fixed width. My questions is whether or not this would enable faster searching of the column (other than it just being small in size). – Monophonic

No, it would not. As the linked document explains, SQLite uses dynamic field types. Even if you declare a field as being INTEGER, you can still store floats, text, or even blobs in it. The "INTEGER" declaration gives the field a preference for storing data as integers, but does not restrict it to doing so. It would still be a variable-width column/record. (And that's even ignoring the detail that INTEGER isn't fixed-size in SQLite anyhow...) – Relay

Answer 2

Since SQLite uses variable-length records only, I would guess they did not implement fix-width lookup optimization when rows happen to have the same length.

And as Dave pointed out, one can still store text in INT fields. Since SQLite never truncates data, this means SQLite permits seemingly fixed width column like INT to store variable-length data too. So it is impossible to implement fixed-width lookup optimization.

Gabe