SQLite can use more than one index for a query

I was trying to figure out if SQLite has the ability to use more than one index as part of executing a single query, or if it only ever picks a single index that it thinks will give the best performance.

1.5. Multiple AND-Connected WHERE-Clause Terms in the query planner documentation gives the impression that the single best index is selected if there are multiple options:

So which index, Idx1 or Idx2, will SQLite choose? If the ANALYZE command has been run on the database, so that SQLite has had an opportunity to gather statistics about the available indices, then SQLite will know that the Idx1 index usually narrows the search down to a single item (our example of fruit='Orange' is the exception to this rule) whereas the Idx2 index will normally only narrow the search down to two rows. So, if all else is equal, SQLite will choose Idx1 with the hope of narrowing the search to as small a number of rows as possible.

I tried that on my global-power-plants table, which has an index owner and another on country_long:

explain query plan
select
  country_long,
  count(*)
from
  [global-power-plants]
where
  owner = 'Cypress Creek Renewables'

Sure enough, that query indicates that only one of the indexes was used:

SEARCH TABLE global-power-plants USING INDEX "global-power-plants_owner" (owner=?)

Interesting to note that explain query plan select country_long, count(*) from [global-power-plants] reports using a covering index scan:

SCAN TABLE global-power-plants USING COVERING INDEX "global-power-plants_country_long"

But SQLite uses multiple indexes for UNION queries

On a hunch, I decided to try the following query:

explain query plan
select country_long, count(*) from [global-power-plants]
union all
select owner, count(*) from [global-power-plants]

To my surprise, this returned results that indicated both indexes were used:

So there are queries for which multiple indexes can be used at the same time.

Related

• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite List all columns in a SQLite database - 2020-05-06
• sqlite The SQLite now argument is stable within the same query - 2023-02-05
• sqlite Identifying column combination patterns in a SQLite table - 2021-01-12
• sqlite Counting SQLite virtual machine operations - 2022-03-20
• datasette Searching all columns of a table in Datasette - 2021-08-23
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• sqlite Splitting on commas in SQLite - 2021-02-01
• sqlite Combining CTEs and VALUES in SQLite - 2023-01-29

Created 2022-12-11T23:34:00-08:00, updated 2022-12-11T23:34:38-08:00 · History · Edit