On Saturday, January 29, 2011 at 5:54 PM, Rael Bauer wrote: > The table contains an id field with a unique index and another field with a > non-unique index. > > > > So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command > > (or some graphical equivalent) to duplicate that file. How long does it > > take ? > > > > > > You seemed to miss what I was saying: > If the blob field is positioned in the middle of the columns then even If I > don't include the blob field in the query the select is very slow (1.5 > seconds). > If the blob field is positioned at the end of the columns then if I don't > include the blob field in the query the select is very fast (140 ms). If I do > include the blob field, the select is about 400-500 ms. > > > > >
That is a known feature (issue). I am sure it is written up somewhere, but definitely, on this list, Richard Hipp and others have emphasized this many times -- search the mailing list archives. Best design -- keep the blob in a separate, dedicated table, and join to that table only when the blob is to be retrieved. That way, sqlite doesn't have to plow through useless pages to find stuff that would fit in a single page but doesn't because of the intervening blob. -- Puneet Kishor Sent with Sparrow _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
