Will I run into performance issues if I use a blob field as primary key in SQLite?

Question

I have a sqlite database where all the primary keys are GUIDs. Currently they are stored as fixed length strings but I want to store them as blobs because it simplifies the code for storing and retrieving data. I converted part of the database and everything is functioning as expected. However, I'm not sure if I will run into performance issues.

For example, would a statement like this be faster on strings than blobs?

SELECT * FROM table1 t1, table2 t2 WHERE t1.id = t2.parent_id

My intuitions says no, but that doesn't really mean anything.

Answer 1

The best way to find out is to run the queries against a profiler/SQLite's timer. Setup a test and run the query 1000 times with string, then 1000 times as a blob. Winner is the fastest.

Intuition is one thing, hard data is another.

Answer 2

I think AIEE and if I were you I'd be storing GUIDs in a pair of Integer types on SQLITE (SQLITE INTEGER is 64 bits).

However in this case blob might actually work better.

LFSR is right, profile it.

Answer 3

Why you shouldn't use it

A primary key is often indexed and used for sorting. A BLOB cannot be indexed which makes it the slowest of all datatypes. In fact, it is the worst choice as primary key and most databases, including the SQL99 standard, disallow it.

The problem with a BLOB is that its datatype is not known by the database (a BLOB should only be used for anything undefined, like an logo, an image, a word document, that can only be stored as binary data). Hence it cannot optimize it. Another problem is display. A blob cannot simply be displayed as text.

Most SQL implementations do not allow BLOB fields compared, but SQLite allows it. However, it converts anything your compare it to into a blob and then compares it bit by bit.

Best alternative

The best option for a primary key column in SQLite is to use the INTEGER PRIMARY KEY as described here;: http://www.sqlite.org/lang%5Fcreatetable.html#rowid it gives the best performance (it is already there as the rowid column, it is just aliased).

Conclusion

To answer your question: yes, it influences the performance badly. But more importantly, it will make it very hard to manage your tables well. Use INTEGER PRIMARY KEY, it is really the best, guaranteed unique and is blazingly fast.