Learning Mysql
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- Table Types | 267
- USE music mysql> SHOW TABLE STATUS;
| Table Types In the book so far, we’ve used only the default MyISAM table type. Theresa good reason behind this you very rarely need to make any other choice in small- to medium- size applications because it’s a very fast, reliable table type for most tasks. However, at the time of writing, there are at least nine other choices you can make. This section gives you an insight into these choices, explaining briefly the pros and cons of the alternatives to MyISAM. You can divide the MySQL table types up into two sets using a few different criteria. The most common division is transaction-safe (TST) versus non-transaction-safe (NTST): Table Types | 267 transaction-safe tables (TSTs) These include the InnoDB and the (no longer supported) Berkeley Database (BDB) table types. TSTs support transactions and have advanced features that allow you safely restore and recover from database failures. Non-transaction-safe tables (NTSTs) These include the MyISAM, Merge, and Memory (also called Heap) types described in this section. They’re less advanced than the TSTs, but that isn’t always bad. They’re typically much faster to query because there’s less overhead, and they use much less disk and memory space. They’re also much easier to understand. We’ve avoided TSTs in this book, because you’re unlikely to need to configure, set parameters for, and use such tables for most applications. Another consideration when choosing a table type is whether it supports foreign key constraints. With foreign-key support, you can tell MySQL that a row in a table shouldn’t exist without another matching row in another table. For example, you could use it to stop you from adding anew album for an artist who doesn’t exist. We don’t use foreign-key constraints, and instead rely on the application to do the checking, not the database. Doing the checking in the database slows everything down because MySQL needs to verify the foreign-key constraints before it modifies anything. It also prevents you from ignoring the rules for good reasons—such as improved performance —when you want to. Currently, only the InnoDB table type supports foreign-key constraints, although support is planned for MyISAM. If you’re not using the InnoDB table type and specify foreign-key constraints fora field, MySQL won’t complain, but won’t actually do anything, either. We won’t discuss foreign key constraints in further detail. You can use the SHOW TABLE STATUS command to display technical information about how your tables are stored: mysql> USE music mysql> SHOW TABLE STATUS; +--------+--------+---------+------------+------+----------------+-------------+... | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |... +--------+--------+---------+------------+------+----------------+-------------+... | album | MyISAM | 10 | Fixed | 13 | 133 | 1729 |... | artist | MyISAM | 10 | Fixed | 6 | 131 | 786 |... | played | MyISAM | 10 | Fixed | 11 | 11 | 121 |... | track | MyISAM | 10 | Fixed | 153 | 138 | 21114 |... +--------+--------+---------+------------+------+----------------+-------------+... ... +-------------------+--------------+-----------+----------------+... ... | Max_data_length | Index_length | Data_free | Auto_increment |... ... +-------------------+--------------+-----------+----------------+... ... | 37436171902517247 | 2048 | 0 | NULL |... ... | 36873221949095935 | 2048 | 0 | NULL |... ... | 3096224743817215 | 2048 | 0 | NULL |... ... | 38843546786070527 | 5120 | 0 | NULL |... ... +-------------------+--------------+-----------+----------------+... ... +---------------------+---------------------+------------+-------------------+... ... | Create_time | Update_time | Check_time | Collation |... ... +---------------------+---------------------+------------+-------------------+... Download 4.24 Mb. Share with your friends:
|