3 ms·
Why would you want to recorder columns? SQLite reads in a whole record at a time to access any column.
by dkjaudyeqooe 3y ago
Why would you want to recorder columns? SQLite reads in a whole record at a time to access any column.
- anacrolix 3y agoNot true.
- masklinn 3y agoBecause as in structs padding slack can lead to a surprising amount of overhead.
- dkjaudyeqooe 3y agoThat's not the case: "SQLite does not pad or align columns within a row. Everything is tightly packed together using minimal space." https://sqlite.org/forum/info/06ad7f81fea46401 https://sqlite.org/forum/info/06ad7f81fea46401
- simonw 3y agoI reorder columns all the time for neater readability of "select *" queries.
- prirun 3y agoOne reason to reorder columns with SQLite is that if a column is usually null or has the default value, SQLite will not store the column at all if it is at the end of the row. It only saves a couple of bytes per column, but it is a reason to get these columns at the end.
- masklinn 3y agoAFAIK position has nothing to do with nulls, a null is a 0 byte in the header and has no payload in the row: https://www.sqlite.org/fileformat.html#record_format https://www.sqlite.org/fileformat.html#record_format
- prirun 3y agoContinue reading that section: "Missing values at the end of the record are filled in using the default value for the corresponding columns defined in the table schema." If you have a table with 5 columns and you only insert the first 3 columns (based on create table column order) because the last 2 values are null or default, SQLite will only insert 3 type bytes in the header. However, if the first column (in create table order) is the one you omit, SQLite has to include its type byte, even if the value is null.