Using SQLite How do i index columns in a CREATE TABLE statement?

Question

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 4bytes

Answer 1

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)

Answer 2

Just replace filesize INTEGER NOT NULL with filesize INTEGER UNIQUE NOT NULL. The unique keyword will create the index.

You can also execute a separate CREATE INDEX command.

Answer 3

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.