Tuesday, August 4, 2020
dbametrix
More
    Home Database Technology MySQL Table Types

    MySQL Table Types

    This blog post explains about types of Mysql tables with characteristics

    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,

    - Advertisement -
    dbametrix

    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.

    0
    Previous articleMySQL and virtualization
    Next articleHelp tool of mySQL

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here

    - Advertisment -
    dbametrix

    Most Popular

    MySQL as PHP database

    The article illustrates the relationship between data and semantics of data stored in a database with reference to the semantic WEB

    Supply Chain Management in ERP

    The article provides detail of what is supply chain management system and what is the importance.

    Business Intelligence Market

    The blog explains how business intelligence is growing in Global market and what is the future of BI.

    Peer to Peer Technology

    The article explains what is the peer to peer p2p technology and how it works and helps to improve your knowledge.

    Recent Comments