At 10:47 PM 6/16/2011, Johan De Meersman wrote:
----- Original Message -----
> From: "Bennett Haselton" <benn...@peacefire.org>
>
> Do you happen to know the answer to my other problem -- if I have
> TEXT and BLOB columns but all my other columns are fixed-length, can
> I still get the benefit of faster lookups resulting from fixed-length
> rows, if each row just contains a fixed-length reference to the
> actual TEXT or BLOB data which is stored elsewhere?
entirely unsure, but given the single-datafile structure of MyISAM
tables, I strongly suspect BLOBS get stored inline.
I can't find a source that says for sure.
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
does say: "The maximum size of a row in a MyISAM table is 65,535
bytes. This figure excludes BLOB or TEXT columns, which contribute
only 9 to 12 bytes toward this size... For BLOB and TEXT data, the
information is stored internally in a different area of memory than
the row buffer." But that's talking about memory, not disk. When
people talk about performance improvements from using fixed-length
rows, are they talking primarily about memory or hard disk?
Hold up though, I just got this reply from posting the question in a forum:
http://forums.mysql.com/read.php?21,423433,423846
which says "Almost always the discussions recommending Fixed length
records in MyISAM are myths. The apparent performance improvement is
swamped by the performance loss of shoveling around the wasted
padding bytes" and goes on to give reasons.
Actually, that does make sense that it's a myth. I was surprised to
hear so many sources claiming that there was a big performance
increase from being able to find row N by jumping to position
N*rowlength. Because even with variable-length rows, you can just
store a table associating row numbers with the position of the row in
the file, can't you -- which would mean it would only take one
near-instantaneous lookup to be able to jump to the row you're looking for.
What I was really trying to figure out was why it takes me 4 hours to
add a new column to my 22-million-row table, and whether a different
table design can avoid that problem. That reply in the forum says,
"ALTER TABLE ... ADD COLUMN will always copy the entire table over,
and rebuild all the indexes. (And, effectively, do an OPTIMIZE.) Do
_not_ leave space for extra columns, it won't help." I'm about to
reply and point out the trick that you suggested to me: create dummy
columns early and then just rename them later :)
-Bennett
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
