[MySQL] TEXT

[Chris]

I have 99.999% read MyISAM table to hold elements of layout (or as a buffer).

There are several table entries, each has ID and 5 TEXT fields (60k chars max each) used for various purposes. Now, should I go into direction of increasing number of entries (5 TEXT fields each) or increasing the number of fields per entry? When it comes to usability there is almost no difference (the table is used for thousands purposes, some need to be fast, some just store information that is rarely needed).

Technicly, the proper approach would be to make each entry ID and 1 TEXT. This would be the most flexible and no waste would occur. But for some frequently read things I need several TEXT fields at once, so instead of reading 5 entries with 1 TEXT field I just read 1 entry with 5 fields which should be gentlier on SQL.

I would have no dilemma if this was fixed width table with fields being INTs not TEXT. I would just make aboundance of INTs and ignore that half of them is usually not used. But this is variable width table, also it contains TEXT not VARCHAR, so the data is not exactly stored in the body of the table (wonders where/how exactly TEXT is stored in MyISAM).

Any advices, thoughts?

[andrewjbaker]

OK... nobody has commented... so I'll throw in my 2p worth.

You haven't mentioned performance at all. So what are your motivations for either approach?

I would just make aboundance of INTs and ignore that half of them is usually not used.

Is disk space at a premium?

1. Go with what works and is the simplest solution for now.

2. Change it later if it becomes a problem.

[Chris]

Performance.

In the past I was doing it via regular message system (admin account message the announcement to news account and all the messages from the news account were displayed as announcements). The system bown up after reaching 2700 daily players (was working flawlessly till 2500 players).

So, the number of entries in mostly read table that use TEXT matters. It can kill the database.
What's interesting, it is not the heavier use table, still it was the one that blown things up. This indicate that TEXT is *evil* and should be treaded with care...

Now I simply made a separate table just for announcements and other related stuff, but this is still the same TEXT approach. Now I wonder, is the performance affected by number of entries or the total number of TEXT fields in database? Should I put more fields in one entry or more entries with fewer fields?

[andrewjbaker]

You really need to try and find out exactly what it is that's causing your DB to fall over before you can make reliable efforts to combat it.

If it is a 'size' problem, MySQL has a range of different 'TEXT' types with differing storage sizes. Have a look at TINYTEXT to see if it'll be sufficient.

[Chris]

No, it is opposite. I have no problem at all, everything works flawlessly. I just want to protect for unexpected and to find out the best THEORETHICAL solution/approach to proper use of TEXT.

TEXT is the smalles 64kb text format, so it is the proper one to use in this case...

Does anyone know how TEXT is stored in MyISAM? In the body of the table, or in separate table? If only pointed is stored is the TEXT containing table considered fixed width and only have a separate data file that holds the text but it is not used for regular search except for the text search?

BTW, what's the difference between VARCHAR, VARCHAR(x), TINYTEXT?

[Zeggy]

TEXT/BLOB are all stored in the same file as the rest of the data, the only difference is that it's not inline, instead the row object contains a pointer to the position in the file where the data is stored. TEXT is stored the same as BLOB, just 2 bytes for length of data + data.

TINYTEXT uses 1 byte for length.

VARCHAR is 1 byte for length and then data. In mysql 5 it uses 2 bytes for length. This is stored inline with the rest of the row objects.

I'm not sure about the difference between VARCHAR, VARCHAR(x). I think MySQL treats varchars as chars internally sometimes.

[Chris]

Much clearer now, thanks.

So, if we use TEXT does it make the table fixed width for search purpose (assuming the criteria is not TEXT)? Since TEXT is pointer + length it is always the same size so the start of a new row can be calculated by multiplication... Or maybe the TEXT data is put in the middle of the file and distort the width of row since you don't know where are rows and where data of TEXT?

Is TINYTEXT stored like TEXT or like VARCHAR?

I have heard that VARCHAR, VARCHAR(x) differs during sorting. If you specify (x) the engine will allocate bigger buffer, always for max (x). So the memory could go bloated and in such case no (x) specified might be better. Unconfirmed gossip.

[Zeggy]

If you use blob/text at all, the table will always be treated as dynamic width, even if the row structure itself is fixed width (I'm not even sure if it is). So for example, the index file will be stored differently as well.

TINYTEXT is stored the same as TEXT, it just has a smaller length limit (so less overhead).

Given a choice between varchar or text, just go with varchar unless you need to store larger amounts of text.

I'm not sure about sorting.

[Zeggy]

I found this table comparing storage engines, maybe others will find it interesting too: http://i.imgur.com/Tb9DL.png