MyISAM only -- Normally a record is a continuous stream of bytes in the
.MYD file. This includes
BLOB columns. An index has a byte offset (or record number) to point into the .MYD file.
After row(s) have been
DELETEd, there can be holes in the .MYD. MyISAM prefers to fill in the holes before appending to the .MYD. However the hole(s) may not be big enough. In this case, part of a record is stored in the first hole and a link (byte offset) is stored to the next part of the record. This linking goes on as long as necessary.
So, if there is a lot of "churn" in the table (deletes+inserts, etc), then new
INSERTs can be very inefficiently stored. That is, they may be fragmented and scattered. This is one of the few cases where
OPTIMIZE TABLE is useful for a MyISAM table. The
OPTIMIZE will rebuild the table by effectively reading and rewriting each row into a new (tmp) .tmd, later to be renamed to .MYD. This defragments rows and gets rid of any remaining gaps.
There is no "block" structure to the .MYD. In contrast, the .MYI, holds all indexes, including the
PRIMARY KEY, in 1KB BTree blocks.
The only caching for data is what the OS provides. That tends to be in 4GB chunks that have zero correlation to record boundaries of the rows in the table.
When to have parallel table -- The question is really about this. (But I needed to say the above first.)
It is hard to give a clear indication of when it is useful to manually split the table by columns.
Case 1: You do a lot of searching through the table without touching the TEXT field. Having the big TEXT field(s) else where avoids stepping over those big cow paddies. The
JOIN to get the TEXT for one row is not that expensive.
Case 2: You are always doing
LIKE on the TEXT field. Then vertical partitioning slows things down.
I have done Case 1 in a few projects.
Case 1 can be further enhanced by this trick:
SELECT a.stuff, b.text
FROM ( SELECT ... FROM main_table ... LIMIT 1 ) a
JOIN text_table b USING (id);
- The subquery hits a lot of rows, but then decides on a few (via
GROUP BY, or...);
- Any tmp tables are not big (since the TEXT is not in it);
- Only 1 probe into the parallel table are needed.
Another trick is to Compress the text field (and put it into a BLOB).
- Do the compression in the client, not the server
- 3:1 shrinkage is typical for text
- The space savings (hence I/O savings) is likely to more than compensate for the CPU time in compression if you have a big table.
(The two "tricks" can be applied to InnoDB, to.)