SQLite Forum

Help speeding up this query
For this part of the query:

with maxes(ProjID, pl_insert, pd_insert, pe_insert)
  as (
      select projid,
             (select max(insertdate) from project_dashboard where ProjID = e.projID),
             (select max(insertdate) from project_extras where projid = e.projid)
        from project_list e
    group by projid
select * 
  from maxes

See if that is what is taking the greatest part of the time.  You may be able to speed up the query even more by optimizing this part which basically generates the set of candidates based on the (projid, insertdate) for each of the other tables.

The remaining conditions in the original query are basically just "culling and sorting" those candidates.