SQLite Forum

What indexes help Window Functions
The 'one index per query' is not correct.  The number of indexes used will generally be limited to one each per table involved in the query.  Note that something like:

 select a,
         select sum(c)
           from t
          where a == o.a
    from t as o
order by b

uses two tables and each one will use a different index (if available).  The most efficient index for the outer query is t(b) (to be able to traverse the rows of t in order and avoid having to sort) and that the most efficient index for the correlated subquery will be a covering index t(a,c) to permit the appropriate traversal of the table in order and generating the sum without accessing the underlying table).

The CLI has a `.expert` command that will attempt to tell you what indexes, if available) would produce the least-cost solution.