where to put TEXT field? 2

[RobBroekhuis]

I have a table to which I want to add, optionally for each record, a rather long descriptive text, as a TEXT field. Only some fraction of the records will have this field filled, and the field will not be used in queries (besides to retrieve its value). I can either put this field right in the main table, or create a child table with a link back to the primary key of the main table. Is the second approach more efficient with database space than the first?

Rob

 

[sleipnir214]

It depends on what percentage of records will have text to store.

TEXT column types use L+2 bytes to store a value, where L is the length of the text in the column. I assume an empty TEXT column will take 2 bytes.

I also assume you're using columns of type INT (which requires 4 bytes per record) to relate the two tables.

If you store the text in the main table, the total storage required with the TEXT column in the main table is 2 bytes per record plus the total number of bytes of text stored for all records.

If you have the TEXT column in a separate table, the storage will be 6 bytes (4 for the relating INT, 2 for the TEXT) per record plus the total length of all text stored.

Let R be the number of records and P be the percentage of records that contain text. Since the total number of bytes of text stored is the same, the formula looks to me like:

[tt]
2 R > 6 RP
2 > 6 P (assuming R not zero)
2/6 > P[/tt]

It looks like 1/3 of the records' having text is the break-even point. If you anticipate that less than 1/3 of your records will store text, use a separate table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[hos2]

or to put it in other words. there is no diskspace reservation if you don't have any text

 

[r937]

but total space is not the entire answer

if you split the big field off into its own table, then the main table will remain relatively small

therefore more rows per physical page on disk

therefore more rows returned per single disk fetch

therefore faster queries



rudy

http://r937.com/

SQL Consulting

 

[RobBroekhuis]

Thanks for your input. I kind of knew what Sleipnir stated, but it was good to have the numbers worked out. I was wondering about considerations such as the one Rudy raised. For my tables (small so far), I don't really think it matters much, so I'll stick with the single-table approach, but I may have to benchmark different approaches as the size gets larger.
Cheers


Rob