CODEWITHSUNDEEP
sqlsqliteselectindexingquery-optimization
David542
David542

Reputation: 110163

Index on part of a column in SQLite

Is doing something like the following possible in SQLite:

create INDEX idx on mytable (synopsis(20));

In other words, indexing by something less than the full text field? This is useful on long-text fields where I don't want to index everything into memory (the index itself could take up more space than the entire table).

Upvotes: 1

Views: 368

Answers (1)

GMB
GMB

Reputation: 222472

You seem to be looking for an index on expression:

Use a CREATE INDEX statement to create a new index on one or more expressions just like you would to create an index on columns. The only difference is that expressions are listed as the elements to be indexed rather than column names.

Consider:

CREATE INDEX idx ON mytable(SUBSTR(synopsis, 1, 20));

Please note that, as explained in the documentation, for this index to be considered by the sqlite query planner, you need to use the exact same expression that was given when creating the index.

So this query would use the index:

SELECT * FROM mytable WHERE SUBSTR(synopsis, 1, 20) = 'a text with 20 chars';

While, typically, this would not:

SELECT * FROM mytable WHERE synopsis LIKE 'a text with 20 chars%';

Note: yes, 'a text with 20 chars' is 20 chars long...

Upvotes: 3

Related Questions