On Thu, Jul 6, 2017 at 3:56 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Dominique Devienne wrote:
> > It's a group-by query, so despite using the index, all rowids for the
> only
> > 4 different "index entries" must still be counted,
> > and that's still definitely longer to do that than full scanning the
> table
> > once.
>
> So why using GROUP BY? The top-level query does not really want all
> groups, it wants to determine individual counts.
>
> I estimate that a simple "SELECT count(*) FROM tab WHERE code_type = ?"
> would be faster.
>
And you'd be right! Thanks a bunch. That's helpful.
704ms with the index, 3,010ms w/o it. The latter is still > 2x faster w/o
the 22MB index penalty.
(select count(*) from well_keys where code_type = 'SET') as "#sets",
(select count(*) from well_keys where code_type = 'CST') as
"#constants",
(select count(*) from well_keys where code_type = 'CMT') as
"#comments",
(select count(*) from well_keys where code_type = 'LOG') as "#logs",
Now though, I'm intrigued as to why the group-by is so much slower than the
"unrolled" count(*)-where... --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
