Hello! I've been trying probably for ages to make the following work. I have a table with following schema (most of the columns are irrelevant for now):
sqlite> .schema Event CREATE TABLE Event (id integer, assignedCPUs varchar, bringsSchedule integer not null, clock integer, deadline integer, expectedEnd integer, expectedStart integer, job integer, jobHint integer, neededCPUs integer, neededHDD integer, neededPlatform varchar, neededRAM integer, virtualClock integer, parent_fk integer, sourceMachine_id integer, targetMachine_id integer, type_id integer, primary key (id)); CREATE INDEX tIndex ON Event (type_id); CREATE INDEX testIndex ON Event(sourceMachine_id, parent_fk, virtualClock); 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. I've been messing around with indexes literally for hours and I wasn't able to get it running properly. Perhaps I'm just missing something. Would you please show me the light or perhaps just tell me where the torch is? :-) I even found this [1] mailing, but there was no explanation as to why the solution is better and thus I couldn't have tweaked it for my scenario. Any help greatly appreciated. Best regards! [1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg29676.html -- Lukáš Petrovický http://www.petrovicky.net/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
