The structure of a record is shown in the graphic at this link which is from my book SQLite Forensics:
www.sqliteforensics.co.uk/pics/table_leaf_format.png As long as ALL of the serial types (i.e. all of the cell pointer array) is held in the main B-tree (i.e. not an overflow page) which unless you have a huge number of columns in a table will pretty much always be the case, then finding out the offset to a given field in the payload is a case of just reading and determining the size of each serial type until you get to the entry you want. If the payload data you want is in the same page, before or after a blob, then accessing it is just a case of reading from the current page buffer. It is only when the blob is so large that the data you want is in an overflow page that things slow down, it shouldn't matter if some of the record overflows, as long as the data you want is on the b-tree leaf page. If the blob is very big then you may need to read multiple overflow pages to get to the data you want. It is also worth bearing in mind that this does not just apply to blobs, if you have a large string field, or multiple string fields that cause a record to overflow, then you have exactly the same problem with records that come after the string fields. Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 6 June 2018 at 10:15, Hick Gunter <h...@scigames.at> wrote: > Your schema has a major flaw that is addressed in SQLite documentation: > > Do not put any fields after blob fields, especially if the content is > typically large. SQLite accesses fields in the order of defintion, so to > access your ANH_PRC_id field, it has to retrieve the 1MB ANP_VALUE blob. > > Putting small and frequently acessed fields at the beginning of the > definition allows SQLite to retrieve these fields without expanding the > whole row. > > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Aydin Ozgur Yagmur > Gesendet: Mittwoch, 06. Juni 2018 11:06 > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Betreff: [EXTERNAL] Re: [sqlite] Sqlite delete slow in 4GB > > Thank you very quick response. > > We have already planned to change file system. But this problem seem not > related with the file size, because we did not hit the limit and when i try > to delete same data in ,for example, 2 GB-sized db, i encounter with the > same problem. This is my table and index definitions. > > Thanks for your help. > > CREATE TABLE "ANHXT" ( > "id" integer primary key autoincrement, > "ANH_AD" text, > "ANH_DBGMHWID" text, > "ANH_TYPE" integer, > "ANH_INDEXNO" int64_t) > CREATE TABLE "PRCXT" ( > "id" integer primary key autoincrement, > "ANP_SEGMENTNO" integer not null, > "ANP_VALUE" blob, > ==> "ANH_PRC_id" bigint, > constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references > "ANHXT" ("id") on update cascade on delete cascade deferrable initially > deferred) CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE > INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT( ANP_SEGMENTNO,ANH_PRC_id)CREATE > INDEX findex on PRCXT( ANH_PRC_id) > > > > On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter <kbille...@gmail.com> > wrote: > > > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote: > > > > > I have been using fat32 file system. I have a database which has 4 > > > GB > > size. > > > > Er... I'm surprised there aren't more problems due to 4GB being the > > max file size supported by fat32. Any chance to change it to exFAT? > > > > > > K > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
