Types of tables in MySQL and characteristics
I got so many comments and queries about this topic, due to this reason I am publishing this articles for my readers and friends.
MySQL supports the following types of tables:
MyISAM type tables,
Tables type InnoDB,
MERGE type tables,
HEAP type tables,
We can take one by one reference of this table type to see it’s characteristic.
First we can see detail of MyISAM type tables.
MyISAM type tables have the following characteristics:
Each table is represented on the disk by a file that describes its format with the extension .frm, by a file that contains data with the extension .MYD and by a file containing the indexes with the extension .MYI. All files are stored within the database directory;
The AUTO_INCREMENT clause is more flexible than all other types of tables;
They can be used to create MERGE type tables;
They can be converted into compressed, read-only, very fast tables;
They support the FULLTEXT search type;
They support table-level lock. In reading, access is allowed simultaneously to multiple queries, while in writing an exclusive lock at table level is used;
InnoDB type tables
InnoDB tables have the following characteristics:
Each table is represented on the disk by a file that describes its format with the .frm extension, while the data and indexes are written within one or more files used as tablespace common to all tables of this type;
This type of table supports transactions, operations defined within the BEGIN, COMMIT, ROLLBACK statements …
InnoDB provides a system for the automatic recovery of data in the event of a MySQL server crash or of the pc on which the server is running;
InnoDB supports foreign keys and referential integrity constraints;
Concurrency management for queries is managed through multi-versioning and row-level lock;
MERGE type tables
MERGE tables have the following characteristics:
A MERGE table is the result of the set of multiple MyISAM tables. Each MERGE table is represented by two files, one with the .frm extension that contains the definition of the table structure and one with the .MRG extension containing the list of MyISAM files that make up the tables joined in the MERGE;
Querying a MERGE table involves querying all the tables that make it up;
A MERGE table can be useful when you need to store large amounts of data being a logical unit that far exceeds the maximum data storage capacity of a MyISAM table;
HEAP type tables
HEAP tables have the following characteristics:
Each table is represented on the disk by a file that describes its format with the .frm extension, while data and indexes are written in memory. It follows that this type of table is very fast, on the other hand the data stored in these tables does not survive the restart of the server;
HEAP tables use a large amount of memory, therefore they are not used to store large amounts of data.
Dbametrix is the remote DBA experts team only provides Oracle and SQL on all platform including cloud services. You should need to read our this article comparison of Oracle and MySQL to improve your knowledge about databases. Stay connected with our this Expert DBA Team Club Blog or official Dbametrix’s blog page.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
