--- "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > I wanted to know how SQLITE works internally. Assume I am doing
Try using the EXPLAIN command on your queries: EXPLAIN SELECT foo from bar; EXPLAIN SELECT * from bar; http://www.sqlite.org/lang_explain.html > But if SQLITE uses some other buffer then this might cause problem as > copying the whole data will certainly take more time than copying the > two columns. Selecting unnecessary columns add extra column opcodes to the select loop and can take up extra space in temp store for sub-queries. Here's a concrete example of how "select *" can be slower than just selecting the specific column(s) you need: $ ./sqlite3 slow.db .dump BEGIN TRANSACTION; CREATE TABLE n(a); INSERT INTO "n" VALUES(1); INSERT INTO "n" VALUES(2); INSERT INTO "n" VALUES(3); INSERT INTO "n" VALUES(4); INSERT INTO "n" VALUES(5); INSERT INTO "n" VALUES(6); INSERT INTO "n" VALUES(7); INSERT INTO "n" VALUES(8); INSERT INTO "n" VALUES(9); INSERT INTO "n" VALUES(10); CREATE VIEW v as select n1.a-n5.a k,* from n n1,n n2,n n3,n n4,n n5,n n6; COMMIT; $ time ./sqlite3 slow.db "select min(k) from (select * from v limit 999999);" -9 6.99user 0.23system 0:07.24elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+4042minor)pagefaults 0swaps $ time ./sqlite3 slow.db "select min(k) from (select k from v limit 999999);" -9 4.63user 0.13system 0:04.81elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+4036minor)pagefaults 0swaps You can see that using "select *" takes roughly 50% more time in this case. The difference in time depends entirely on your specific query and the data. ____________________________________________________________________________________ Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
