cstrader wrote:
I'm looking to enhance query speed. Does it matter what variable type I use as the primary key? I'm thinking numbers might be faster than strings, but I'm not sure.
Thanks!
Integer primary keys will be the fastest since SQLite optimizes this
case by not creating a separate index for the key. The integer key is
stored directly as the rowid in the btree used to store the row data. To
find a row it looks up the key value in the rowid of the table's btree.
For other primary key types, SQLite creates an index that stores the key
field and the rowid of the row data in the btree. This index is stored
in another btree. To find a row it looks in the index btree to find the
rowid of the row that matches the key value, then it looks up the data
in the table btree using the rowid it got from the index.
I believe that SQLite uses memcmp() to compare the key values for all
key types. So there is no benefit to using numeric values over text as
long as the text is usually different within the first few characters.
It doesn't do floating point compares using floating point instructions
for example (which could be done in a single instruction on most modern
CPUs).
HTH
Dennis Cote
