Hi Lukáš,
> And I've been trying to run the following query:
>
> sqlite> SELECT * FROM Event WHERE bringsSchedule = 0 AND
> sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT
> NULL ORDER BY id DESC LIMIT 1;
> 835|29|0|309493|||||0|||||334|834|9||
> CPU Time: user 0.027995 sys 0.000000
>
> As you can see, it takes ages and it shouldn't. When I don't use any
> "ORDER BY" clause, I get:
>
> sqlite> SELECT * FROM Event WHERE bringsSchedule = 0 AND
> sourceMachine_id = 9 AND virtualClock <= 1000 AND parent_fk IS NOT
> NULL LIMIT 1;
> 238||0|146026|||||0|||||95|236|9||
> CPU Time: user 0.000000 sys 0.000000
>
> Please note that this one is lightning fast, while the one above it
> is not.
You don't mention how many rows in your table. Are you aware that the
"limit" filter occurs after everything else, including the "order by"?
So if your query returns a million rows, the million rows will be
tabulated and sorted before your query then just picks one. That
explains why it takes much longer when using "order by" even though
you are only returning one row.
A better/faster approach would be to filter the returned rows in the
query, rather than using a limit. Something like:
SELECT * FROM Event
where id =
( SELECT max(id) FROM Event
WHERE bringsSchedule = 0
AND sourceMachine_id = 9
AND virtualClock <= 1000
AND parent_fk IS NOT NULL
)
;
I think you'll need just the following index:
CREATE INDEX EventIndex ON Event (bringsSchedule, sourceMachine_id,
virtualClock, parent_fk);
Hope this helps,
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
