As a probable variation of creating indexes with a "based on ..." principle in mind, I wonder how hard it is (for majority of cases) to implement something like
<blockquote>
SUGGEST INDEX { sql query}
</blockquote>
command. So given a query, it suggest necessary indices if they don't exist already.
Basically the code should parse the query and find all index-related pieces like ORDER BY, GROUP BY, WHERE in the query, identify fields and field sets and suggest new indexes if necessary.
I suspect that although SQLite itself is best fit for the task, a third party tool may also be a good candidate using the SQLite as a help. For example, since EXPLAIN QUERY PLAN output usually contains Scan {table} lines, it can be used for feedback loop in this tool, so the tool may create a memory-based db with the same schema (and no data) and iterate with different create index command analyzing whether scan {table} lines are gone or not.