Thank you! How about separate DB just for large texts? Would that be an overkill? We could use different page sizes for the two DBs. I'm not sure how well transactions over several DBs are handled though.
Best regards, Igor -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 08, 2007 12:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Optimizing performance by moving large texts into a separate table "Igor Sereda" <[EMAIL PROTECTED]> wrote: > We have a database that can possibly grow into millions of rows. Some > = tables have TEXT fields, which may store texts of signigicant > length. = All other data is mostly numeric values. > > We have a thought of moving all large texts into a separate table, and > = replacing text_column with text_id in the rest of the schema. The = > assumption is that db pages are allocated fully to a single table, so > = the numerical part of the database will end up in a few db pages and > so = we'll be able to quickly run queries over them. (We won't have > queries = for texts, only look-ups by text_id.) > > Is our assumption correct? Is that a pattern someone here has = > implemented maybe? How does the size of the whole database affect = > queries to a single table? > This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
