SQLite - INDEXED By Clause

SQLite - INDEXED BY Clause

The INDEXED BY clause is a non-standard SQL feature unique to SQLite that acts as a query planner hint. It instructs SQLite to use a specific index when querying a table, overriding the query planner’s default choice.

Conversely, the NOT INDEXED clause instructs SQLite to avoid using any indexes for a table, forcing a full table scan. The INTEGER PRIMARY KEY may still be used for lookups even with NOT INDEXED.

Caution: SQLite’s query planner is highly optimized. In most cases, it will select the best index automatically. Forcing an index with INDEXED BY should be a last resort, used only when you have evidence that the planner is making a suboptimal choice.

Syntax

The clause is placed immediately after the table name in SELECT, DELETE, or UPDATE statements.

SELECT ... FROM table_name INDEXED BY index_name WHERE ...;

SELECT ... FROM table_name NOT INDEXED WHERE ...;

If the specified index_name does not exist or cannot be used for the query, the statement will fail to prepare.

Example Scenario

Consider a CUSTOMERS table with two indexes: one on country and one on last_active_date.

CREATE TABLE CUSTOMERS (
  id INTEGER PRIMARY KEY,
  name TEXT,
  country TEXT,
  last_active_date DATE
);

CREATE INDEX idx_country ON CUSTOMERS(country);
CREATE INDEX idx_last_active ON CUSTOMERS(last_active_date);

-- (Imagine this table is populated with millions of rows)

Now, consider a query to find highly active customers from a very common country.

SELECT name FROM CUSTOMERS
WHERE country = 'USA' AND last_active_date > '2023-01-01';

The query planner might choose idx_country because the country = 'USA' condition is simple. However, if 90% of customers are from the ‘USA’, this index is not very selective. Using idx_last_active would be much more efficient. You can force this choice:

SELECT name FROM CUSTOMERS INDEXED BY idx_last_active
WHERE country = 'USA' AND last_active_date > '2023-01-01';

When to Consider Using It

• Performance Tuning: When you have used EXPLAIN QUERY PLAN and determined that the query planner is consistently choosing a less efficient index for a critical query.
• Complex Queries: In very complex queries with multiple joins and WHERE clauses, the planner’s statistical analysis might be imperfect.
• Ensuring Consistency: To lock in a specific query plan for predictable performance, although this can make your code brittle.

Risks and Downsides

• Brittleness: Your query is now tied to a specific index name. If the index is renamed or dropped during a refactor, the query will break.
• Suboptimal Performance: If data distribution changes over time, your forced index choice may become worse than the planner’s automatic selection.
• Reduced Readability: It adds non-standard syntax to your queries, making them harder for other developers to understand.