Dominique Devienne wrote: > with > kcounts(t, c) as ( > select code_type, count(*) > from keys > group by code_type > ), > ... > select ..., > (select c from kcounts where t = 'SET') as "#sets", > (select c from kcounts where t = 'CST') as "#constants", > (select c from kcounts where t = 'CMT') as "#comments", > (select c from kcounts where t = 'LOG') as "#logs", > ...; > > the SQL as written does several passes over kcounts
However, the subquery is flattened: each pass counts only rows with a specific code_type value. An index on code_type would help _very much_. > Is there a way to reformulate this query differently to somehow achieve a > single pass on the keys table? Or force the query planner to "instantiate" > the kcounts "CTE view" to achieve that single scan, via some kind of hint? It would be possible to prevent the subquery flattening (http://www.sqlite.org/optoverview.html#flattening), but AFAIK there is no hint that would force SQLite to recognize that all the instances of kcounts are then identical and can be reused. You could put kcounts into a temporary table. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
