Using two index's in sql slow

Databases
31

What I would do (short experience with SQLite, haven’t used index yet), is try to find where the problem is, for that I would like to know:

  • you said the table has 160k records
  • how many records >= 2018-10-17 ?
  • fromname is an index, are those unique records ?
  • sqldatetime (because it has a time component) are those close to unique too ?

Can you post the result of:

select count(distinct fromname) from mydata
select count(distinct fromname) from mydata where sqldatetime >= '2018-10-17'

and how long it takes:

select sqldatetime,rowid,fromname from mydata where sqldatetime >= '2018-10-17' group by fromname
select fromname,count(*) from mydata group by fromname

Maybe the index is the problem, maybe the group by, maybe other thing.

Because this is fast:

select sqldatetime,rowid,fromname from mydata where sqldatetime >= '2018-10-17'

Can you copy the result information to a new table (mydata_temp) without index, then do:

select fromname,count(*) from mydata_temp group by fromname
  • you don’t need the where sqldatetime here (I think)

Note: maybe I made mistakes to the above, I hope the idea is clear and you find a way to get your results faster.

34

Im still sure that the format of the query is the bulk of the issue

ONLY select the fields you want to group by, (eg not RowID)
and although it shouldnt make a difference, Id use count(1) not count(*) just in case its bringing stuff in which it shouldnt.

Not

select sqldatetime,rowid,fromname,count(*) from mydata where sqldatetime >= '2018-10-17' group by fromname

But

select fromname,count(1) from mydata where sqldatetime >= '2018-10-17' group by fromname

To me, including the rowid has got to make it consider every row.
Count() makes no sense in the original query

37

Just to be sure…

  1. You’re working with an SQLite database.
  2. The attachments are in a separate table, not in the main mailbox table.

Could you post the table create SQL for the Mailbox table? Also the indexes.

Assuming 1 & 2 above. What happens if you copy your database and drop all other tables (for sure the attachments), then run a vacuum command. Now what time do you get with your (previously 19 second) query?

40

What is the size of a vacuumed database with only that table? From the field names I didn’t see any that would indicate large amounts of text in a single field. Is the speed faster when you create a new database with only the mailbox table?

41

This:

select count(distinct fromname) from maiblox where sqldatetime >= '2018-10-17'

I guess will return 293

Can you do:

explain query plan select fromname,count(*) from mailbox where sqldatetime >= '2018-10-17' group by fromname

to see if the query is using a b-tree or index

42

[quote]select fromname,count(1) from mydata where sqldatetime >= ‘2018-10-17’ group by fromname

Wow! , the above command took 230 seconds to complete, not sure why it was 10x slower![/quote]

Sounds like fromname is not indexed…

[quote]select fromname,count(*) from mailbox where sqldatetime >= ‘2018-10-17’ group by fromname
returned 293 records and took 17.384[/quote]

But hang on… that SHOULD be the same query. Assuming all you changed was 1 to *
But the table name is different (mydata vs mailbox) - is that a typo or the cause?
There shouldnt be any reason why (*) works faster than (1)

[quote]select fromname,count(1) from mydata where sqldatetime >= ‘2018-10-17’
took 0.07th of a second (although it only returned one record?[/quote]

You aren’t grouping by fromname. I hazard you are getting ‘the last fromname it looked at’
Many RDBMS engines wouldn’t even allow this query.
It’s just counting the records and giving you guff in the fromname field.

43

Dave,

Try this:

create temporary table temptable as select sqldatetime,rowid,fromname from mailbox where sqldatetime >= '2018-10-17'; select *, count() from temptable group by fromname; drop table temptable;

48

If you’re using SQLite Studio, it doesn’t support combining execute and select statements, you’ll need to run the three statements in three separate runs. A better option is to download Valentina Studio Free. It can run the query I posted and give you a proper result.

49

There must be something wrong with your database. You need to try the suggestion to copy just this table to a new database - no indexes or anything else. Then try the query on that. I created a test table with over 300k records spanning dates from 1/1/18 to 10/20/18 and the following query runs in 288 ms:

select parentid, count(*) from test where timestmp>='10/18/2018' group by parentid

I am doing this in Valentina Studio. I wonder if your sql utility is the problem. Doing the above should confirm whether it is.

50

Could very well be.

Dave, What app are you using.

I just checked SQLite Studio gives wrong results, While DB Browser (no time with my query), and Valentina Studio give correct results.

53

What version of Xojo are you using?

55

Well as others already said, it doesn’t make sense (less to me because I don’t have much experience).

For me, the create temporary table should work or at least give different results.

Have you tried creating a view and do the count() there?

  • Create a view with sqldatetime, rowid, fromname where date >= ‘2018-10-17’
  • make sure it show data with select * from view_name
  • do the select fromname, count() from view_name group by fromname

I hope you find a way to make it work.

56

I just downloaded SQLite Manager and can verify that is the problem. Good grief it even crashed when I tried pasting my query into it… arg

From Xojo try this:

[code] Dim sql As String
sql = “create temporary table temptable as select sqldatetime,rowid,fromname from test where sqldatetime >= ‘2018-10-17’;” _

  • “select *, count() from temptable group by fromname;” _
  • “drop table temptable;”
    Dim rs As RecordSet = mdb.SQLSelect(sql)
    ’ rs is nil select because of multiple statements

mdb.SQLExecute(“create temp table temptable as select sqldatetime, rowid, fromname from test where sqldatetime > 99950;”)
Dim rs As RecordSet = mdb.SQLSelect(“select *, count() from temptable group by fromname;”)
mdb.SQLExecute(“drop table temptable;”)
'rs contains records[/code]

57

I don’t have any indexes on my test. That’s why I’m saying there has to be some problem with your database. I bet you’ll find that a simple export to csv then import to a new table in a new database will fix the problem.