I'm looking for some tips & tricks documentation that explains how
different data types in rows are stored at the file level (in MyISAM
tables, at least), and how to optimize tables for faster queries,
updates, table definition modification, etc. based on this knowledge.
For example, I've heard that if all of your columns are fixed-length,
that makes it faster to find individual rows since row N is located
at position N*rowlength in the file. (And, presumably, having just
one variable-length column will slow things down considerably.) But
I've also read that having TEXT and BLOB columns will also slow down
row-finding operations. This seems to make no sense because I
thought TEXT was not actually stored in the row, but the row just
stored a constant-length reference to the TEXT whose actual data was
stored somewhere else. Is this correct? Then is it incorrect to say
that a TEXT column will slow down the locate-row-N operation,
assuming all other columns are fixed-length? This is the kind of
thing I'm looking for a document to explain.
Another example: It sometimes takes me an extremely long time to add
new columns to a table. What if I had a table with all fixed-length
columns, and I "reserved" some space at the end of each row to be
used for columns to be added in the future. Would it then be
possible to add new columns much more quickly? You wouldn't have to
move around the existing row data to make room for the new column
(although presumably you would still have to *write* to the place in
reach row where the new column had just been defined, to fill it in
with its default value).
In particular, I'm not looking for a list of optimization tricks, so
much as a document that explains how the rows are stored at the file
level, and thereby explains how the optimization tricks *follow
logically from* this information. The reason is that if I just have
a grab-bag of optimization hints (of which I've found many on the
Web), some of them will be not applicable to my situation, or just
plain wrong, and I'll have no way of knowing which ones. But if you
know *why* something works, you can more easily figure out if it
applies to your situation.
-Bennett
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
