On Thu, 2006-04-06 at 17:18, Antimon wrote: > Hi, > I need to store text entries and i use text datatype. I want to ask if > it will be better to split text and entry information? > > I mean, i can use a table like, (id, authorid, insertdate, editdate, > threadid, textdata) or i can have an entrytexts table (id, entryid, > textdata) and a foreign key on entryid -> entries.id. > Which would be better? I might need to do some range searches and > orders on entries so i thought splitting text might decrease some > overhead? > > Or shall i just use one table? Text over a certain size gets moved out of the main table and stored in the toast table, so there's not that huge of a hit in terms of performance. It's really a question of relativity. If you're non large text fields will add up to a couple hundred bytes, there's no great gain moving the text to another table, and when you join them, you've got the overhead of joining two separate tables. OTOH, if you'll be storing one int, one date, and one 10 character or so text keyword or something, then it might be worth your while to move the text out. If you're always gonna grab the text at the same time, leave it in the table. If you'll grab it once every 1,000 or so accesses, separate may be better. Nothing beats a benchmark. But knowing that the database automagically compresses and stores text (over a certain size) helps you realize why you won't get huge returns on moving the text to another table.
