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