CODEWITHSUNDEEP
sqlite
benjist
benjist

Reputation: 2881

SQLite index usage with different type affinity

I have a simple table "tags" containing a key and a value column. The key is always a string, the value can be either string, int64 or a double value.

I do not have any real data at this point to test with. But I'm curious about the index usage of the value column. I've defined the column as TEXT type - is SQLite still able to use the index on the value column when an int64 or double type is bound to the statement?

Here is the test table:

CREATE TABLE "tags" ("key" TEXT,"value" TEXT DEFAULT (null) );
INSERT INTO "tags" VALUES('test','test');
INSERT INTO "tags" VALUES('testint','1');
INSERT INTO "tags" VALUES('testdouble','2.0');

I see additional "Integer" and "Affinity" entries when analyzing the query via:

explain SELECT value FROM tags where key = "testint" and value >= 1

But I do not see any difference in index usage otherwise (e.g. idxgt is always used). But I'd rather like to have a definite answer rather than relying on wrong assumption with the small test data.

Upvotes: 0

Views: 152

Answers (1)

CL.
CL.

Reputation: 180020

The documentation says:

A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.

The sort order is well-defined for all types. Forcing the affinity to be TEXT makes comparisons on this column with numbers behave as if the values were text, but that is probably what you want.

In any case, indexes do not change the behaviour; they work correctly with all types, and apply affinities in exactly the same way as on non-indexed columns.

Upvotes: 1

Related Questions