CODEWITHSUNDEEP
sqlite
qrdl
qrdl

Reputation: 34958

How to create SQLite index for different datatype

When I create index on certain column, index is datatype-aware, however I can store data of any type in any column, but it makes the index somewhat useless if I want to compare column content using different comparison rules, e.g. compare numbers as numbers, not strings.

Example:

sqlite> create table foo (key varchar, value varchar);
sqlite> create index foo_ndx on foo (key, value);
sqlite> insert into foo values ('bar', 10);

sqlite> select * from foo where key = 'bar' and value > 9.0;
sqlite> explain query plan select * from foo where key = 'bar' and value > 9.0;
0|0|0|SEARCH TABLE foo USING COVERING INDEX foo_ndx (key=? AND value>?) (~2 rows)

In this case 9.0 gets implicitly casted as varchar and two varchars get compared, index is used fully, but the record cannot be found.

sqlite> select * from foo where key = 'bar' and CAST(value AS REAL) > 9.0;
bar|10
sqlite> explain query plan select * from foo where key = 'bar'
   ...> and CAST(value AS REAL) > 9.0;
0|0|0|SEARCH TABLE foo USING COVERING INDEX foo_ndx (key=?) (~5 rows)

In this case I cast value to real explicitly and two reals get compared, sqlite finds the record, but the index used only partially.

This is understandable behaviour, however can I somehow create index on foo table that will treat column value as REAL? I've tried using CAST in CREATE INDEX but got syntax error, I can create view on foo table with CAST, but index cannot be created on view.

Upvotes: 1

Views: 669

Answers (1)

CL.
CL.

Reputation: 180020

Indexes are not data type aware.

By default, values in the value column are treated as text, if possible, because the column has text affinity. Using an index does not change this behaviour.

If you do not want to treat numbers as text, do not declare the column as varchar. (Note: you can declare columns without any data type.)

Upvotes: 1

Related Questions