# Short version
I'm with you on adding INDEXes to CTE but I hate your syntax proposal. :)
# Long version
## I feel your pain
Actually, I've written a lot of large queries where having such a feature would have been a time saver. It would have also prevented me from rewriting a "clean" CTE-query into a complex write-only equivalent (I was unable to understand it after a few hours even with extensive comments.)
## No prior art
There is no other SQL implementation that has such a feature (or at least no implementation that I am aware of.)
So the syntax has to be "invented". My reasoning is that it should also fit with the SQL way of expressing things: I mean it has to avoid mixing a heavy DDL syntax (CREATE INDEX) in a relatively pure query syntax (the CTE.)
## The proposal
My suggestion is twofold:
* Extend [`table-contraint`](https://www.sqlite.org/syntax/table-constraint.html) with an INDEX branch. As a bonus, it will make creating INDEXes possible from inside CREATE TABLE. [Note that it is already possible to do so in MySQL](https://dev.mysql.com/doc/refman/8.0/en/create-table.html). The suggested extension will reuse parts I highlighted in the current CREATE INDEX syntax:
```pikchr
linerad = 10px
linewid *= 0.5
$h = 0.21
circle radius 10%
A0: arrow 2*arrowht
CR: oval "CREATE" fit
UQ: oval "UNIQUE" fit with .w at (linewid right of CR.e,.8*$h below CR)
IX: oval "INDEX" fit with .w at (linewid right of UQ.e,CR)
arrow from CR.e right even with UQ; arrow to IX.w
arrow from CR.e right linerad then down even with UQ then to UQ.w
line from UQ.e right linerad then up even with IX then to arrowht left of IX.w
arrow from IX.e right
oval "IF" fit
arrow right 2*arrowht
oval "NOT" fit
arrow 2*arrowht
ETS: oval "EXISTS" fit
# IF NOT EXISTS bypass
Y1: .5*$h below UQ.s # vertical position of back-arrow
arrow from IX.e right linerad then down even with Y1 then left even with UQ
arrow from ETS.e right then down even with Y1 then left even with ETS.w
line left even with IX.w
# second row
arrow left even with first circle then down $h*1.25 then right 2*arrowht
SN: oval "schema-name" fit
arrow 2*arrowht
DOT: oval "." bold fit
arrow
oval "index-name" fit
arrow 2*arrowht
oval "ON" fit
arrow 2*arrowht
oval "table-name" fit
arrow 2*arrowht
LP: oval "(" bold fill red fit
arrow
IXC: box "indexed-column" fill red fit
arrow
RP: oval ")" bold fit fill red
# Loop over indexed-column
CMA: oval "," bold fit at $h*1.25 below IXC fill red
arrow from IXC.e right linerad then down even with CMA then to CMA.e
line from CMA.w left even with 2*arrowht left of IXC.w \
then up even with IXC then right linerad
# schema-name bypass
arrow from (first circle,SN.n) down even with $h below SN \
then right even with SN
line right even with arrowht right of DOT.e then up even with DOT \
then right linerad
# WHERE clause and end circle
WH: oval "WHERE" fit with .w at IXC.w+(linewid,-$h*3.5) fill red
arrow right 2*arrowht
WEX: box "expr" fit fill red
arrow right even with linewid*1.5 right of RP.e
circle same fill red
# Linkage from the last ")" to the WHERE clause or or circle
arrow from RP.e right linerad then down even with CMA
J1: previous.end
line down even with last circle then to arrowht left of last circle.w
arrow from J1 down even with $h below CMA then left even with CMA
arrow left even with 2*arrowht left of WH.w then down even with WH \
then to WH.w
```
* Extend CTE declaration to allow `table-contraint`s to be used inside the column names declaration (just as what one can already do in CREATE TABLE).
## Your example using my suggested syntax
```
WITH
LookupTable( TimeStamp, LookupContent, PRIMARY KEY( TimeStamp ) ) as MATERIALIZE (
select substr(Content,1,24) as TimeStamp
, trim(substr(Content,26,40)) as LookupContent
from VirtualLookupTextTable
where instr(Content,'keyword')
)
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
```
## Another example using INDEX inside the columns definition of the CTE
```
WITH
LookupTable( a, b, INDEX (a, b), INDEX (b) WHERE a < 10 ) as MATERIALIZE (...)
SELECT ...
```