Why subquery have their place… in this instance it would have added no value, as the same actions happen.
FYI. A “GROUP BY” while it might use the index to select items, it still does a internal ORDER BY to “group” the records to be counted, so the more sparse the physical arrangement is, the longer it might take (no index will “fix” that)
Just for fun, try
where sqldatetime >= '2018-10-17'
ORDER BY fromName
) group by fromname
and why select rowid and email if you never use them?
I’m surprised this works at all. I guess sqlite is more lax than other sql databases. Others require every non-aggregated field to be included in the Group By list, so in this case sqldatetime and rowid should both be included with fromname in the Group By list.
For example, I assume you have multiple differing sqldatatime values for each fromname, so how can it list the sqldatetime value in the results? How does it decide which of the different values to show? And what use is a single value out of a set of differing values of use to you? Same thing applies to rowid.
Can you post an example of the output of the above query?
Me too. What is is supposed to return?
If you include rowid without an aggregation you might expect every row to be returned , with a count of 1
But as Jay says, in Oracle and SQL Server (which I work with most), the query is invalid.
You can return a field if you group by it, but any field which is NOT part of the group by has to be aggregated using
MIN(), MAX(), count() or similar.
How does this query work instead?
select sqldatetime,fromname,count(rowid) from mydata where sqldatetime >= '2018-10-17' group by fromname,sqldatetime
select fromname,count(1) from mydata where sqldatetime >= '2018-10-17' group by fromname
Supposedly rowid is “required” in order to get a timing on query … something I do not understand, since SQLite (as well as every other DB engine I have ever used) has that as a DB function independant of the data content…
But I am ashamed to admit… that I missed the major point that both Jay and Jeff mentioned…
where group by is concerned every field in the Select should either be part of the Group By or used in an aggregate function
but it seems for SQLite at least, it is “lax” and for those fields not in the Group by returns the LAST value it encounters
note : last may or may not mean most “recent” or greatest…
We still need an answer to Neil’s questions. Also, what platform/hardware is this running on? Regardless of the issues of grouping in this instance, I find it difficult to believe a 19 second time for 160k records.
if you have any picture or binary or pdf inside the datas, it can rise the size very fast.
a 200MB database jumped to 2.5GB after 6 months since we add a pdf inside it only …
it is only 60k records (and not all of them have a stored pdf)