Using SQLite how do I index columns in a CREATE TABLE statement?

How do I index a column in my CREATE TABLE statement? The table looks like

command.CommandText =
    "CREATE TABLE if not exists file_hash_list( " +
        "id    INTEGER PRIMARY KEY, " +
        "hash  BLOB NOT NULL, " +
        "filesize INTEGER NOT NULL);";
command.ExecuteNonQuery();

I want filesize to be index and would like it to be 4 bytes

Solution 1:

You can't do precisely what you're asking, but unlike some RDBMSs, SQLite is able to perform DDL inside of a transaction, with the appropriate result. This means that if you're really concerned about nothing ever seeing file_hash_list without an index, you can do

BEGIN;
CREATE TABLE file_hash_list (
  id INTEGER PRIMARY KEY,
  hash BLOB NOT NULL,
  filesize INTEGER NOT NULL
);
CREATE INDEX file_hash_list_filesize_idx ON file_hash_list (filesize);
COMMIT;

or the equivalent using the transaction primitives of whatever database library you've got there.

I'm not sure how necessary that really is though, compared to just doing the two commands outside of a transaction.

As others have pointed out, SQLite's indexes are all B-trees; you don't get to choose what the type is or what portion of the column is indexed; the entire indexed column(s) are in the index. It will still be efficient for range queries, it just might take up a little more disk space than you'd really like.

Solution 2:

I don't think you can. Why can't you use a second query? And specifying the size of the index seems to be impossible in SQLite. But then again, it is SQ_Lite_ ;)

create index
    myIndex
on
    myTable (myColumn)