sql - How do I utilize a partial index created using a LIKE clause in SQLite? - Stack Overflow

admin管理员组

文章数量:1401241

I'm learning SQL and I'm trying to create a partial index on one of my database tables using a LIKE clause. Although the index does get created successfully, when I check the query plan for a query that's supposed to utilize the index created, it shows that SQLite is scanning the entire table instead.

I tried using an IN Clause to accommodate all my LIKE regex matches, but that didn't work either.

The courses table contains the following columns (schema query below):

CREATE TABLE IF NOT EXISTS "courses" (
    "id" INTEGER,
    "department" TEXT NOT NULL,
    "number" INTEGER NOT NULL,
    "semester" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    PRIMARY KEY("id")
);

I've written a query to create an index on the semester table as follows:

CREATE INDEX "course_semester" ON
"courses" ("semester")
WHERE 1=1
AND (
"semester" LIKE '%2023'
or "semester" LIKE '%2024'
)

This index gets created successfully.

However when I check the query plan for the below query, SQLite says it isn't using this index at all.

SELECT "department", "number", "title"
FROM "courses"
WHERE 1=1
AND "semester" = 'Fall 2023';

What do I do to resolve this ? I tried using an IN clause hardcoding 'Fall 2023' and 'Spring 2024' and that didn't work as well.

本文标签： sqlHow do I utilize a partial index created using a LIKE clause in SQLiteStack Overflow