QT SQL advice

[enricong]

I just remembered the "Group By" sql argument.
so I think I can just do:
SELECT SUM(value) FROM database WHERE group=x AND (range > y AND range < z) GROUP BY id

[Lesiok]

I just remembered the "Group By" sql argument.
so I think I can just do:
SELECT SUM(value) FROM database WHERE group=x AND (range > y AND range < z) GROUP BY id

Of course but I think that it should looks like :

SELECT id, SUM(value) FROM database WHERE group=x AND (range > y AND range < z) GROUP BY id

SELECT id, SUM(value) FROM database WHERE group=x AND (range > y AND range < z) GROUP BY id

To copy to clipboard, switch view to plain text mode 

Without it you will not know what is the id of the sum.

[wysota]

Could you please take your queries, launch an sqlite console against your database and execute each query prepending it with "EXPLAIN QUERY PLAN"? E.g. modify a "SELECT SUM(value) FROM database WHERE group=x AND (range > y AND range < z) GROUP BY id" to become "EXPLAIN QUERY PLAN SELECT SUM(value) FROM database WHERE group=x AND (range > y AND range < z) GROUP BY id". Paste the results here, please.

[enricong]

Yes, I forgot the id when I typed up that message

I get the following with explain:
SCAN TABLE database
USE TEMP B-TREE FOR GROUP BY


This now runs in about 80ms so about 400X faster.
Typically, I need to do this about 32 times. Over all the user is waiting about 10-15 seconds so its slowing down elsewhere.
Ideally, I'd like an instant response but I think it's acceptable right now.

[wysota]

If you get a table scan then you are missing an index on the field from WHERE clause (likely 'group' column in your case).

[enricong]

I had a bug where I was creating the index. now its about 50% faster down to about 30-40ms from 80.