CODEWITHSUNDEEP
sqlsqlite
q.b
q.b

Reputation: 69

Best data type for SQLite index

I have a column in SQLite containing a physical ID of an object.

This physical ID is 16 bytes of information. I want to create an index on this column to optimize queries' performance.

What's the best data-type for this column for the index to have the best performance? Should I use a text containing my 32 char long hex, an int8(16), or some other data type?

Upvotes: 1

Views: 1672

Answers (2)

Mike W.
Mike W.

Reputation: 1375

Typically you would get better performance using an integer-based index. However in this specific case, you would see no benefit. SQLite only stores integers up to 8 bytes. Anything larger is going to be stored as text.

Internally, text and blob are stored in a similar way (text respects the database encoding, blobs are stored as-is.) Both can be indexed, and you will see similar performance between the two. Depending on your schema, data, and queries, any difference between the two may not be noticeable in a meaningful way. At that point, the choice would really come down to which is easier for you personally to work with.

Upvotes: 1

cshu
cshu

Reputation: 5944

Don't use text containing hex. I bet it's slower than BLOB (SQLite supports BLOB PRIMARY KEY). Not to mention it doubles the size.

SQLite has only one integer type. (No int8 or int16.)

If your physical ID is integer, I suggest using INTEGER PRIMARY KEY. It's more convenient than BLOB when you embed values in SQL statements, bind values to SQL statements, or get values from query result.

Upvotes: 1

Related Questions