CODEWITHSUNDEEP
sqlite
stenci
stenci

Reputation: 8481

Sqlite doesn't use some indexes

Executing the following code creates a table with two columns and adds 1 million rows. One column is INT and one is TEXT. Then it creates one index per column and one collate nocase index per column. Then it executes three queries.

The first query uses the index t2 as expected.

The second query is the same as the first one, but it adds the ESCAPE clause and doesn't use the index. The presence of unescaped % or _ should prevent the index from being (fully) used, but the presence of the ESCAPE clause itself shouldn't.

Why does the ESCAPE clause prevent the index from being used?

The third query is the same as the first one, but it doesn't use the index. The only difference is that the query uses column col_i instead of col_t which is defined as INT instead of TEXT. Sqlite doesn't prevent me from creating the index, so I would expect for it to be used.

Why isn't the index i2 used?

.timer on
DROP TABLE IF EXISTS tab;
CREATE TABLE tab (col_t TEXT, col_i INT);
INSERT INTO tab (col_i, col_t) WITH RECURSIVE cte (x, y) AS (SELECT hex(randomblob(16)), hex(randomblob(16)) UNION ALL SELECT hex(randomblob(16)), hex(randomblob(16)) FROM cte LIMIT 1000000) SELECT x, y FROM cte;
CREATE INDEX t  ON tab (col_t);
CREATE INDEX t2 ON tab (col_t COLLATE nocase);
CREATE INDEX i  ON tab (col_i);
CREATE INDEX i2 ON tab (col_i COLLATE nocase);
SELECT * FROM tab WHERE col_t LIKE 'abcabcabc';
SELECT * FROM tab WHERE col_t LIKE 'abcabcabc' ESCAPE '\';
SELECT * FROM tab WHERE col_i LIKE 'abcabcabc';

Upvotes: 1

Views: 68

Answers (1)

CL.
CL.

Reputation: 180020

The documentation documents when the index can be used for LIKE:

  1. The left-hand side … must be the name of an indexed column with TEXT affinity.
  2. The right-hand side … must be … a string literal … that does not begin with a wildcard character.
  3. The ESCAPE clause cannot appear on the LIKE operator.
  4. The built-in functions used to implement LIKE … must not have been overloaded using the sqlite3_create_function() API.
  5. […]
  6. … the column must indexed using built-in NOCASE collating sequence.

The query optimizer has to prove that using the index cannot change the meaning of the query. These rules implement the proof.

While there exist queries that would work with the index despite violating these rules, it would be necessary to extend the optimizer to be able to prove that they work.

Upvotes: 2

Related Questions