At 05:46 AM 6/14/2011, Carlos Eduardo Caldi wrote:
Hello Bennett
On the Mysql developer site have a grate documentation, try the
links above.
http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html
http://dev.mysql.com/doc/refman/5.0/en/data-size.html
Thanks, this gets me a little closer to the answer but doesn't really
provide the level of detail that I'm looking for. For example, it
says: "For MyISAM tables, if you do not have any variable-length
columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is
used. This is faster..." I still don't understand: If TEXT and BLOB
columns are stored not by putting the data in the row but by having
the row store a reference to the TEXT/BLOB data stored somewhere
else, then can't a row with TEXT and BLOB data types *still* be a
fixed-size row, with the resulting increased speed?
My main motivation is that I have a table with 22 million records and
it takes a few hours for me to add a new column to it. I suspect
this would be faster if I designed the table correctly from the
beginning, and I want to change it to a smarter design, but I only
want to do it once. So I want to understand really thoroughly *why*
a different design would make it faster to complete the table
modifications. (For example, the question I asked earlier about
whether you can declare extra space at the end of each row that is
"reserved for future columns".)
Att.
Carlos,
> Date: Tue, 14 Jun 2011 01:44:47 -0700
> To: mysql@lists.mysql.com
> From: benn...@peacefire.org
> Subject: optimization strategies based on file-level storage
>
> 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=ce_ca...@hotmail.com
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
