On 14/04/2009 6:29 AM, Lukáš Petrovický wrote:
> 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.
I can't see ... why are you measuring CPU time instead of elapsed time?
what is the unit of measure? nanoseconds or centuries?
> 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.
What do you get when you do
SELECT COUNT(*) FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id
= 9 AND virtualClock <= 1000 AND parent_fk IS NOT NULL
?
Is that a small number or a large number? If large, consider the
possibility that in your problem query it is finding all those rows,
sorting them on "id" then throwing away all but the row with the highest
"id".
Perhaps you need something like:
[untested]
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
);
This may well run much faster. Bonus: it says exactly what I presume
that you are trying to do, rather than relying on a side effect of ORDER
BY and LIMIT used in conjunction.
HTH,
John
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
