Optimal way to store 1000+ (30-50 KB) texts in a MySQL table with outer .TXT files?

Question

I would like to store 1000+ records in a table. It has got:

ID : auto increment INT title : varchar (20) author_ID : INT timestamp : unsigned INT text : TEXT (this is the problem) (+tags and categories)

The "text" field has to store about 30-50 KByte texts. But I don't want to search in this field, only in the other ones. Is it a good idea, to store these big texts in .TXT files? Will the database queries be faster?

You can better save the TEXT column(s) in a separated table. And join the table when you need the TEXT column. — Raymond Nijland, Dec 01 '17 at 15:36

score 2 · Accepted Answer · answered Dec 01 '17 at 16:14

2

TEXT and BLOB are stored off the table.

There is only a pointer to the location of the actual storage. I don't think that you have to worry too much, especially if you have only around 1 thousand records.

Anyway, referring to this post, if your data is always < 65 KByte, you can think about using VARCHAR.

It seems that it will have better performances.

More info in the linked Q&A.

answered Dec 01 '17 at 16:14

kiks73

3,718
3
25
52

That post is somewhat out of date. – Rick James Dec 02 '17 at 16:45

score 0 · Answer 2 · answered Dec 02 '17 at 16:49

I would suggest MEDIUMTEXT because some day your 30-50K will grow to 70K. VARCHAR won't go that far

The way InnoDB structures things, there is rarely any not to keep text columns in the database. (Images are another matter.)

There is a pitfall wherein you JOIN two tables, then pare the resultset down with GROUP BY or LIMIT -- If you are carrying big text columns through the temp tables, this can be a performance problem.

Optimal way to store 1000+ (30-50 KB) texts in a MySQL table with outer .TXT files?

2 Answers2