In the context of keeping your queries lean and mean, you need to consider several things in terms of the table design. First, if a frequently queried table also gets a lot of changes, the way to improve performance is to stay with fixed-length fields, rather than variable-length ones. The trade-off is this: By definition, fixed-length fields take up a certain amount of space, regardless of the content, whereas variable-length fields adjust themselves depending on the data entered. Thus, you’re bound to waste more disk space using fixed-length fields. If it’s the speed you’re after, however, MySQL will perform better with fixed- rather than variable-length fields.
If one field in a frequently changing table cannot be formatted to a fixed length, consider moving that field to a separate table and converting the rest of the fields in the original table to fixed-length fields. Although this might not be workable in all circumstances, it is a viable way to achieve the performance gain of using fixed-length fields rather than variable-length fields.
NOTE
InnoDB tables handle row storage differently from MyISAM or ISAM tables. Using fixed-length fields instead of variable-length ones won’t result in a performance boost with these table types.
This being said, if you are going to use fixed-length fields, make sure the field size is kept to a minimum. For example, when designing a table, creating a CHAR(255) field is often easier than worrying about exactly how big you need to make it. In practice, you might find that a field half that size can adequately take care of your needs. Paring the field size not only takes up less disk space, but it also means less I/O when processing—and deleting unnecessary fields entirely can also increase performance.
Another technique to improve performance is to use the OPTIMIZE TABLE command frequently on tables that are modified often (discussed more fully in Chapter 12). Frequent modification results in fragmentation, which, in turn, leads to extra time spent reading unused blocks of space to get at the desired data.
When considering ways of improving performance, check to see if you need all the tables you have set up. Again, when originally designing a table, dividing your data might have seemed like a good idea, but extra tables mean your performance will suffer. Look at the tables you join frequently. Is it possible to combine the data into one table instead? If you find you cannot, for whatever reason, try to match the fields you join. Queries will run more efficiently if joined fields are of the same data type and length.
Leave a Reply