cricketfan <srtedul...@yahoo.co.in> wrote: > I have a composite index on queuedb made up of q_id, date,priority(in the > same order). I am trying to find out if the index is being used for both the > where clause and the order by clause.
Yes it is. > dbsql> explain select * from queuedb where q_id='598535' order by q_id, > date,priority LIMIT 1; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Trace 0 0 0 00 > 1 Noop 0 0 0 00 > 2 Integer 1 1 0 00 Integer 1 in register 1 - a counter to enforce LIMIT. > 3 String8 0 2 0 598535 00 The string literal for comparison, in register 2. > 4 Goto 0 32 0 00 > 5 OpenRead 0 3 0 15 00 Open the table, cursor #0 > 6 OpenRead 2 4 0 keyinfo(5,BINARY,BINARY) 00 Open the index, cursor #2. > 7 SeekGe 2 29 2 1 00 Find an entry in the index that's >= the string literal. > 8 IdxGE 2 29 2 1 01 If the entry is in fact greater than the literal, break out of the loop. > 9 IdxRowid 2 3 0 00 Extract rowid from the index. > 10 Seek 0 3 0 00 Find the row with matching rowid in the underlying table. > 11 Column 2 0 4 00 > 12 Column 2 1 5 00 > 13 Column 2 2 6 00 > 14 Column 2 4 7 00 > 15 Column 0 4 8 00 > 16 Column 0 5 9 00 > 17 Column 0 6 10 00 > 18 Column 0 7 11 00 > 19 Column 0 8 12 00 > 20 Column 0 9 13 00 > 21 Column 2 3 14 00 > 22 Column 0 11 15 00 > 23 Column 0 12 16 00 > 24 Column 0 13 17 00 > 25 Column 0 14 18 00 > 26 ResultRow 4 15 0 00 Build and return a resultset row. > 27 IfZero 1 29 -1 00 Decrement LIMIT counter; if it reached zero, break out of the loop. > 28 Next 2 8 0 00 Advance the index and jump back to the beginning of the loop (unless there are no more entries). -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
