|
|
|
|
| |
OPTIMIZE TABLE works with MyISAM and BDB tables, but defragments only MyISAM tables. A defragmentation method that works for any table type is to dump the table with mysqldump and then drop and recreate it using the dump file:
% mysqldump --opt db_name tbl_name > dump.sql
% mysql db_name < dump.sql
Pack data into a BLOB column. Using a BLOB to store data that you pack and unpack in your application may allow you to get everything with a single retrieval operation rather than with several. This can also be helpful for data that are not easy to represent in a standard table structure or that change over time. In the discussion of the ALTER TABLE statement in Chapter 3, one of the examples dealt with a table being used to hold results from the fields in a Web-based questionnaire. That example discussed how you could use ALTER TABLE to add columns to the table whenever you add questions to the questionnaire.
Another way to approach this problem is to have the application program that processes the Web form pack the data into some kind of data structure, and then insert it into a single BLOB column. For example, you could represent the questionnaire responses using XML and store the XML string in the BLOB column. This adds application overhead on the client side for encoding the data (and decoding it later when you retrieve records from the table), but simplifies the table structure and eliminates the need to change the table structure when you change your questionnaire.
On the other hand, BLOB values can cause their own problems, especially if you do a lot of DELETE or UPDATE operations. Deleting a BLOB may leave a large hole in the table that will be filled in later with a record or records of probably different sizes. (The preceding discussion of OPTIMIZE TABLE suggests how you might deal with this.)
Use a synthetic index. Synthetic index columns can sometimes be helpful. One method is to create a hash value based on other columns and store it in a separate column. Then you can find rows by searching for hash values. However, note that this technique is good only for exact-match queries. (Hash values are useless for range searches with operators such as < or >=). Hash values can be generated in MySQL 3.23 and up by using the MD5() function. Other options are to use SHA1() or CRC32(), which were introduced in MySQL 4.0.2 and 4.1, respectively.
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
