I have some use cases where I read several lookup tables from text files. I do this in WITH clauses where I do filtering and apply formatting for further use steps.

Then in the main query I want to left outer join these tables (based on time stamps and time ranges as the only link of data - loose relationship).

Because my table preparation within the WITH clause is reading text files using one of my virtual table extension, there is no support by indexes; they do not exist.

If I understand it correctly from what I read in all the documentation about SQLite3 over the time then each of the left outer join steps have to scan the pre-prepared tables and cannot (yet) simply lookup an index for faster access.

That's why I suggest to think about an extension of the WITH clause by some syntax to give some additional index information.

Of cause I could use temporary tables and temporary indexes - but I do not want to fill any database (not even a temporary) when it is not necessary - the source of information can remain in their text files. Only my (very complex) query is contained in the SQLite3 database and reading from a subfolder relative allows me to simply copy the database to somewhere else to run the same query with different data without any changes to it.

To give some hints about my use case: I am using SQLite3 version 3.36.0 with several of my extension functions and virtual tables. Windows OS. The result table has 29000 rows and the total query time took 544 seconds to get them while looking up just one table with contained 29000 records. The main table without filtering consists of 255000 lines out of 2 text files with a total of 27MB size (there could be many more of them and also the size could be much bigger). My database which contains the complex query has just 98kB size.

The Query Plan is short but seems to be not as efficient as it could become when supported by an index:

id  parent  notused  detail
3   0       0        MATERIALIZE ViewStateMachineState
6   3       0        SCAN AllStateMachineText VIRTUAL TABLE INDEX 0:
25  0       0        SCAN AllPLCcommLog       VIRTUAL TABLE INDEX 0:
47  0       0        SCAN L

How can accessing of WITH clause generated volatile tables be supported with indexes? At least with one primary index would be great!

The Virtual table could probably help but at the moment of reading the text from file there was not filtering on any extracted data happening yet. And as one and the same virtual table file reading process can be used in independent views it is also not neccessary to generate always all possible index info when the use in the with clause requires just one to support performance.

My proposal: as the WITH clause allows repetition with a comma the separated index could be constructed like a select is done. All is kept temporary and only as much as the whole statement requires and as long as the statement connection lives. That would really be a great push and improvement to many of my use cases, where the complex statement is used only once - to fill the result table for further processing and presentations.

with 
LookupTable( TimeStamp, LookupContent ) as MATERIALIZE (
   select substr(Content,1,24)        as TimeStamp
        , trim(substr(Content,26,40)) as LookupContent 
     from VirtualLookupTextTable
    where instr(Content,'keyword')
),
LookupIndex as (
   create primary key( TimeStamp ) on LookupTable
)
select FromTimeStamp, ToTimeStamp, M.Content, L.Content
  from VirtualMainTextTable   M
  left outer join LookupTable L
               on M.ToTimeStamp>M.FromTimeStamp and L.TimeStamp > M.FromTimeStamp and L.TimeStamp <= M.ToTimeStamp
                                                 or L.TimeStamp == M.ToTimeStamp

EDIT: typo detected and corrected and added content from the lookup table to the final result

I know SQLite has logic where it can create Automatic Indexes to help with queries. Perhaps if that was combined with AS MATERIALIZED hints to let it know it's ok to consider making an index on the materialization of the with statement?

Or is that already the case?

YES, I like your proposal to take the CTE table declaration similar to a "CREATE TABLE" statement to include the index declarations.