|
Home
/ Technical Support / MySQL
Key Offerings:
B2B and B2C E-Business
Solutions
Offshore
Software Development Outsourcing
Strategic Consulting
Offshore Software Outsourcing
ALTOROS Systems
is headquartered in Tampa, Florida and maintains an office
near Boston, Massachusetts and technology development center
in Belarus and Russia. ALTOROS specializes on providing value-added
e-commerce and web-based software development and offshore
software outsourcing services to emerging enterprises helping
them successfully plan and implement business initiatives.
Contact Us for more
information.
MySQL Table Types
As of MySQL Version 3.23.6, you can choose between three basic table formats (ISAM, HEAP and MyISAM. Newer MySQL may support additional table type (InnoDB, or BDB), depending on how you compile it.
When you create a new table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type, the index and data will be stored in other files.
Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option. See InnoDB start.
The default table type in MySQL is MyISAM. If you are trying to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This is a very useful feature when you want to copy tables between different SQL servers that supports different table types (like copying tables to a slave that is optimised for speed by not having transactional tables). This automatic table changing can however also be very confusing for new MySQL users. We plan to fix this by introducing warnings in MySQL 4.0 and giving a warning when a table type is automatically changed.
You can convert tables between different types with the ALTER TABLE statement. See ALTER TABLE.
Note that MySQL supports two different kinds of tables: transaction-safe tables (InnoDB and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).
Advantages of transaction-safe tables (TST):
· Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
· You can combine many statements and accept these all in one go with the COMMIT command.
· You can execute ROLLBACK to ignore your changes (if you are not running in auto commit mode).
· If an update fails, all your changes will be restored. (With NTST tables all changes that have taken place are permanent)
Advantages of not transaction-safe tables (NTST):
· Much faster as there is no transaction overhead.
· Will use less disk space as there is no overhead of transactions.
· Will use less memory to do updates.
You can combine TST and NTST tables in the same statements to get the best of both worlds.
BDB BDB or BerkeleyDB tables
|
