SQLite DELETE slow when primary key is two columns

Question

I have a SQLite table defined as follows:

CREATE TABLE mytable (
  colA INTEGER NOT NULL,
  colB INTEGER NOT NULL,
  colC INTEGER,
  PRIMARY KEY (colA, colB));

One of the inner-loop operations I perform in a large transaction is:

DELETE FROM mytable
  WHERE (colA = ?) & (colB = ?);

This operation seems to be bottlenecking my application. When I run strace to see what the application is doing, I see it making large sequences of sequential 1024-KiB pread64 calls on the database file, suggesting that maybe that the deletion actually requires a linear scan of the table, rather than a simple index lookup.

I'm assuming there's an index on the primary key, but maybe I'm doing something wrong. My question is whether there's a way to speed up these delete operations.

Answer 1

The operator & is the bitwise AND and not the boolean AND operator.

If you use the boolean AND operator then the primary key will be used to make the execution faster:

DELETE FROM mytable
  WHERE (colA = ?) AND (colB = ?);

See the demo with the execution plan.