Databases Reference
In-Depth Information
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 descri-
bed 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 topic, 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 a new 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 con-
straints, although support is planned for MyISAM. If you're not using the InnoDB table
type and specify foreign-key constraints for a 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 |...
... +---------------------+---------------------+------------+-------------------+...
