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.

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.

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.