On Wed, 28 Jul 2004, Rubens Jr. wrote:
>Hi !
>
>Suppose one database with 4 tables : S1, B1, B2 and B3, where S1 (small
>1) has < 100 records, and B1, B2, and B3 (big tables) has > 100,000
>records each. Them entire database has +- 400 MegaBytes. These records
>are inserted in ramdom order : S1, B1, B1, B1, B3, B2, B1, B1 ... etc, so
>I think the database will become 'fragmented'.
The tables will be fragmented.
>The SQL : SELECT * FROM S1 will read all 400 megabytes to find the 100
>records ? (and became slow on a older computer) Or the Sqlite is smart
>enough to seek only the sectors of database and find all records as quik
>as if where only these table on database ?
SQLite will only search the pages used by S1 in this case.
SQLite uses page based storage, and one page can hold data for only one
table or index at a time. The pages are located using a btree index, one
per table and another per index. The btree for S1 will reference only S1
pages.
>Is better to use another database to S1 only ?
No.
>I Want to find these records as fast as possible ...
>The VACUUM 'desfragment' the database ? (make a copy of all tables in
>sequence) ?
Don't think so. VACUUM will compact the database, reclaiming unused pages
and making the database smaller.
If your tables are indexed, then you can make them faster by exporting the
data in index order (sort by index field[s]), dropping and recreating the
table, then re-importing in index order. If you mainly access tables in
index order, this could be a big performance gain as the page cache will
be used in a more optimal manner. If you only access single records at a
time, or in random order, it won't gain you much.
>If so, After a VACUUM the speed of SELECT * FROM S1 will be faster than
>before the vacuum ? (since all records of S1 will be one right after
>another and sqlite has not to scan all 400 Megabytes)
No.
>
>Obs: using version 3.03
>
>Thanks
>
>Rubens Jr.
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
