236 Part II . SQL Essentials Caution The (Web servers)
236 Part II . SQL Essentials Caution The trade-off for Static table speed is disk space. Because each record must be padded with characters to bring it to the defined length, each row consumes the maximum defined disk space, even if the actual data is short. Of course, if each record is exactly same fixed length as the definition there is no additional disk space consumption with a Static table. Dynamic format Dynamic tables minimize disk-space usage because the size of the table depends on the actual size of the data. Note Bytes are stored for each column in a Dynamictable to keep track of the length of the data. If you perform an UPDATEon a data record, the updated data may actually be stored in a different location in the datafile. For example, if you insert a value into a column such as an address and then update that address to a much longer address, the updated column may actually be stored in a different location or fragmented away from the rest of the record. This fragmentation can be repaired with the myisamchk utility. Moving fixed-length or static data columns into another table may accelerate those tables, if it is possible and reasonable to organize your data this way. You should determine whether fragmentation or variable-length columns adversely affects performance enough to warrant dividing the tables. To accomplish this, compare the number of variable-length columns in the table to the number of static length columns. If an index relies on static columns, it may be beneficial to move the variable-length column to another table. However, moving the variable-length column to another table can add overhead as that table is joined with the original table. Caution Dynamic tables can be more difficult for MySQL to repair after a crash. Because updates to the data can cause fragmentation, some pieces of a record may be lost when repairing the table. Statictable repairs are easier because all columns are of the same defined length. Using the following ALTER TABLE statement, I have altered the example table definition shown previously in this section. ALTER TABLE example MODIFY name varchar(10); Note Changing one column from CHAR to VARCHAR changes all CHAR columns to the variable-length VARCHAR format. Running the SHOW TABLE STATUSstatement again as shown in Figure 10-4 shows the row format is now Dynamic (one or more columns in the table is now a variable- length type).
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.