CODEWITHSUNDEEP
sqlitesubstrsql-like
Francisc
Francisc

Reputation: 80455

SQLite Like versus Substr

I want to compare the last 4 characters of a Text field.

Performance wise, should I do:

select * from table where col like '%last';

OR

select * from table where substr(col,-4)='last';

Upvotes: 1

Views: 1807

Answers (4)

Benoit
Benoit

Reputation: 79185

The optimizer overview page, section 4, explains that the LIKE operator is designed to optimize expressions of the kind col LIKE 'something that does not begin with a percent sign%'.

Upvotes: 1

Tim
Tim

Reputation: 5421

You won't be able to take advantage of an index when using the ends-with wildcard:

          LIKE '%.edu'

An alternative would be to store a reversed version of the string in another column, index that reverse-content column, and then use LIKE (or better, the GLOB function [because in several SQLite implementations LIKE() has been overridden, which also prevents index use] passing a reversed version of the string you're looking for. For example, if you were looking for URLs that ended with .edu you would search the reversed-column for values starting-with 'ude.'.

Upvotes: 2

Vinicius Kamakura
Vinicius Kamakura

Reputation: 7778

I tried using EXPLAIN before the queries, to see the virtual machine operations, and the one with the substr() function had 3 more OP codes than the one with LIKE. That doesn't mean it is necessarily slower, but is an indication it might be.

Upvotes: 1

Zento
Zento

Reputation: 335

I don't know which has the better performance, but the first one is much better to read and I would not expect any significant differences in performance.

Upvotes: 1

Related Questions