_____________________________________________________________ > Od: "Simon Slavin" > Datum: 02.10.2011 14:01 > > >On 2 Oct 2011, at 12:49pm, Mira Suk wrote: > >> CREATE TABLE [Data1] (Index INTEGER PRIMARY KEY NOT NULL, Data BLOB, >> Description TEXT) >> vs >> CREATE TABLE [Data2] (Index INTEGER PRIMARY KEY NOT NULL, Description TEXT, >> Data BLOB) >> >> essentially those tables are same. however accessing the column >> "Description" takes a lot longer in Data1 table (it's actually based on size >> of blob column) >> did I hit some implementation detail (aka all columns data are read till the >> one required is returned) ? > >I will let the experts handle this. I do know that if you have either TEXT or >BLOB columns with a lot of data in you never do a 'SELECT *' unless you really >need it. this is not about select * simpliest test case is SELECT DISTINCT "Description" FROM "Data?" - distinct just to force sqlite to process all data before returning.
>> should I actually reorder columns based on expected data / size for SQLite ? > >If there is behaviour like the above, then your ordering should not be based >on data/size. It would be more useful to base it on how often you expected to >access each column. So for example, if you display tables showing >[Description] a lot, but >rarely need the [Data], then it might be fastest to >put [Description] in first. you're correct. the "how often" factor should play main role. however putting integer/double field after blob just because it's less used would not be smart too, same IMHO goes for VARCHAR(N) where N is expected small, that is much smaller than database pagesize. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
