SQLite Forum

index misuse?
Login
Oh. Right - it's a transitive term. Your WHERE clause is:

        t1.c0 == t0.c0 AND t1.c1 == t0.c0;

which implies:

        t1.c0 == t1.c1

(because they're both equal to t0.c0). Therefore it can use an index on t1.c0 for constraints on t1.c1 - like "t1.c1 == t0.c0".

Simple example - index on "y" is used for "x=?":

<code>
    $ ./sqlite3 
    SQLite version 3.36.0 2021-04-07 18:17:53
    sqlite> CREATE TABLE t1(x, y);
    sqlite> CREATE INDEX t1y ON t1(y);
    sqlite> explain query plan SELECT * FROM t1 WHERE y=x AND x=?;
    QUERY PLAN
    `--SEARCH t1 USING INDEX t1y (y=?)
</code>