SQLite Forum

What indexes help Window Functions
Login
Thank you, Simon and Keith.  Your answers make perfect sense.  I had done some other research on the internet and the consensus seems to be that it is best to index on the partition by columns and then on the sort by columns.

But one part of Simon's answer makes me think either this whole exercise needs to be rethought from scratch or I need to give up on making my query more efficient using just indexes.  And that part is the sentence "a query can use zero or one index".

Right now, in my query, I have 14 different windows because I am calculating these stats for 14 different measures.  Obviously each window partitions and sorts by a different measure.  I could create an index for each of them, but if the query can use only one of those indexes, it probably won't make a huge difference.  Either I have to live with the time it takes my query right now (which is not bad at all), or I have to come up with a different query structure where I have one window in each query, and then join up the results of all those queries to get the final output I want.  I have a feeling the latter will be slower than what I currently have even if each of the individual queries are sped up greatly by the creation of better indexes.

Thanks for your insights and help.