Offensive Security Researcher & Quantum Cryptography Analyst

INDEXED BY Clause in SQLite: A Complete Guide with Examples

Abdul Wahab Junaid
INDEXED BY Clause in SQLite

Most of the time, you don’t need to think about which index SQLite uses to run your query — the query planner is genuinely good at figuring that out on its own. But every so often, you’ll hit a situation where SQLite picks an index you weren’t expecting, or you’re debugging a performance problem and need to test how a query behaves with a specific index forced into use. That’s exactly what the INDEXED BY clause is for. It’s a niche feature, but when you need it, it’s invaluable.

In this article, I’ll explain what INDEXED BY does, how to use it correctly, when it’s actually useful, and — just as important — when you should stay away from it.

What Is the INDEXED BY Clause?

INDEXED BY is a SQLite-specific clause that lets you explicitly tell the query planner which index to use when accessing a particular table in a query, overriding SQLite’s own automatic index selection.

SELECT * FROM employees INDEXED BY idx_department
WHERE department = 'Engineering';

Here, instead of letting SQLite decide on its own whether to use idx_department, a different index, or a full table scan, you’re explicitly forcing it to use idx_department.

There’s also a companion clause, NOT INDEXED, which does the opposite — it forces SQLite to avoid using any index at all for that table and fall back to a full table scan.

SELECT * FROM employees NOT INDEXED
WHERE department = 'Engineering';

Why Does SQLite Need This?

SQLite’s query planner uses a cost-based optimizer that looks at the structure of your query, the indexes available, and (if you’ve run ANALYZE) statistics about your data distribution, to decide the most efficient way to execute a query. Most of the time, this works well. But the query planner isn’t perfect, and there are situations — often involving complex queries, unusual data distributions, or missing statistics — where it might choose a less efficient index, or skip using an index altogether when one would actually help.

INDEXED BY exists as an escape hatch for exactly those situations: a way to say “I know better than the planner here, use this specific index.”

Basic Syntax

The general syntax pattern is:

SELECT columns
FROM table_name INDEXED BY index_name
WHERE condition;

It can also be used in UPDATE and DELETE statements, since those also need to identify rows to act on:

UPDATE employees INDEXED BY idx_department
SET salary = salary * 1.05
WHERE department = 'Engineering';

DELETE FROM employees INDEXED BY idx_department
WHERE department = 'Engineering';

A Full Working Example

Let’s set up a table and a couple of indexes to see INDEXED BY in action.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date TEXT
);

CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);

Now, a query that filters on both columns could theoretically use either index:

SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 80000;

By default, SQLite’s query planner picks whichever index it estimates will be more selective — likely idx_department if there are relatively few engineering employees, or idx_salary if high earners are rare. If you wanted to force it to specifically use the department index, regardless of what the planner would otherwise choose:

SELECT * FROM employees INDEXED BY idx_department
WHERE department = 'Engineering' AND salary > 80000;

Important Behavior: INDEXED BY Can Cause Errors

Here’s a detail that catches people off guard and is worth understanding clearly: if you specify an index with INDEXED BY that SQLite determines cannot actually be used to satisfy the query — for instance, because the WHERE clause doesn’t reference the indexed column in a way that the index could help with — SQLite will throw an error rather than silently falling back to a table scan.

-- If idx_salary can't help answer this WHERE clause, this raises an error
SELECT * FROM employees INDEXED BY idx_salary
WHERE department = 'Engineering';

This is actually a deliberate design decision. Unlike a query hint in some other databases (which might be silently ignored if inapplicable), SQLite’s INDEXED BY is a hard constraint: either the specified index can be used, or the query fails outright. This makes INDEXED BY somewhat risky to leave in production code, since a schema change that drops or renames the index will cause every query using it to start failing immediately, rather than degrading gracefully to a slower query.

Using NOT INDEXED

Sometimes you want the opposite: to confirm what a query looks like or performs like without any index assistance, forcing a full table scan.

SELECT * FROM employees NOT INDEXED
WHERE department = 'Engineering';

This is particularly useful for benchmarking — comparing the performance of an indexed lookup against a full scan lets you actually measure how much benefit an index provides, rather than just assuming.

Using EXPLAIN QUERY PLAN to See What SQLite Would Choose

Before reaching for INDEXED BY, it’s worth first understanding what SQLite would do on its own. The EXPLAIN QUERY PLAN statement shows you exactly which index (if any) SQLite intends to use for a given query.

EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 80000;

This returns a human-readable description of the plan, something like showing whether it’s doing a SEARCH using a particular index, or a SCAN of the whole table. Running this before and after adding an INDEXED BY clause lets you directly compare what changes.

When INDEXED BY Is Actually Useful

To be candid: for the vast majority of applications, you will never need INDEXED BY. It’s a specialized tool, and SQLite’s own documentation is fairly explicit that it should be used sparingly. That said, here are legitimate scenarios where it earns its place:

When to Avoid INDEXED BY

ANALYZE;

INDEXED BY vs. Just Adding a Better Index

It’s worth emphasizing this distinction clearly: INDEXED BY doesn’t create anything or improve anything on its own — it only controls which existing index SQLite uses. If your real problem is “there’s no good index for this query,” the fix is to create one, not to force the use of a mediocre existing index.

-- The actual fix, if no good index exists yet
CREATE INDEX idx_department_salary ON employees(department, salary);

A well-designed composite index like this one often eliminates the need for INDEXED BY entirely, because the planner will naturally choose it once it’s the clearly superior option.

Common Use Cases

Best Practices

  1. Reach for EXPLAIN QUERY PLAN before INDEXED BY. Understand what SQLite is already doing before trying to override it.
  2. Run ANALYZE before assuming the planner is wrong. Often the planner just needs better statistics, not a manual override.
  3. Use INDEXED BY primarily for diagnostics, not as permanent production logic, given the risk of hard failures if the index changes or disappears.
  4. If you must use INDEXED BY in production, document why. A future maintainer (including future you) needs to understand this isn’t an accidental leftover from debugging.
  5. Prefer fixing the actual indexing strategy — adding, removing, or restructuring indexes — over forcing SQLite’s hand with a hint.
  6. Test both indexed and NOT INDEXED versions of a query when you’re genuinely trying to measure an index’s real-world benefit.
  7. Re-verify INDEXED BY queries after schema migrations, since renamed or dropped indexes will cause immediate query failures rather than a graceful fallback.

Wrapping Up

The INDEXED BY clause is one of those SQLite features that most developers will go their entire career without needing, and that’s by design — SQLite’s query planner is generally trustworthy, and manually overriding it should be the exception, not the rule. But when you’re debugging a genuinely puzzling performance issue, or you need deterministic, testable control over exactly how a query executes, INDEXED BY (and its counterpart, NOT INDEXED) gives you that precision. Just go in with your eyes open about the tradeoff: what you gain in explicit control, you give up in graceful degradation, since an invalid or missing index reference will cause the query to fail outright rather than quietly falling back to a scan.

Exit mobile version