Skip to main content

MySql Storage Engine

Change table engine to MyISAM

ALTER TABLE `tableName` ENGINE = MYISAM

Change table engine to innodb

ALTER TABLE `tableName` ENGINE = innodb

How to create table to MYISAM

mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM;

Features of MYISAM and INNODB

Feature
InnoDB
MyISAM
Storage limits
64TB
256TB
Transactions
Yes
No
Locking granularity
Row
Table
MVCC
Yes
No
B-tree indexes
Yes
Yes
Clustered indexes
Yes
No
Data caches
Yes
No

MyISAM
  1. MyISAM doesn’t support foreign key constraints or transactions, which are essential for data integrity. Hence we call MySQL with MYISAM is DBMS
  2. MYISAM not supports transaction. You cannot commit and rollback with MYISAM.
  3. MYISAM supports Table-level Locking : In addition, the whole table is locked whenever a record is inserted or updated; this causes a detrimental effect on performance as usage grows.So no other session can perform a SELECT or a DML operation on the table.
  4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  5. Support Full-text indexing.When use "select" statement, gives faster results as compare to Innodb. so you can use MyISAM, if the table is more static with lots of select and less update and delete.
  6. When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size).
    ALTER TABLE mytable ROW_FORMAT=Fixed;
  7. We can you make a MyISAM table's .MYD faster with this
       Notes:
  1. An .frm file stores the table format.
  2. The data file has an .MYD (MYData) extension. 
  3. The index file has an .MYI (MYIndex) extension.
  4. All numeric key values are stored with the high byte first to permit better index compression.


 Innodb
  1. It is an ACID compliant storage engine. 
  2. It supports row-level locking, crash recovery and multi-version concurrency control.
  3. Foreign key constraints : If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB.
  4. Used for Secure Transactions section just like dealing with some payment systems.
     Notes:
  1. When you create an InnoDB table, MySQL creates a .frm file in a database directory under the MySQL data directory. It also create an .ibd file. 
  2. .idb files contain a single table and associated index data
ACID (Atomicity, Consistency, Isolation, and Durability)

Atomicity: Atomicity requires that each transaction be "all or nothing".
When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

Consistency: The database remains in a consistent state at all times.
After each commit or rollback. While transaction data are in consistent mode. i.e  If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

Isolation: Isolation requires that multiple transactions occurring at the same time not impact each other's execution. i.e Transactions are protected from each other while they are in progress.

Durability: Once a commit operation succeeds , results of transactions are durable.
It is property that ensures transactions are saved permanently and do not accidentally disappear or get erased, even during a database crash. 

Labels:

Comments

Post a Comment

Popular posts from this blog

A Guide to UTF-8 for PHP and MySQL

Data Encoding: A Guide to UTF-8 for PHP and MySQL As a MySQL or PHP developer, once you step beyond the comfortable confines of English-only character sets, you quickly find yourself entangled in the wonderfully wacky world of UTF-8. On a previous job, we began running into data encoding issues when displaying bios of artists from all over the world. It soon became apparent that there were problems with the stored data, as sometimes the data was correctly encoded and sometimes it was not. This led programmers to implement a hodge-podge of patches, sometimes with JavaScript, sometimes with HTML charset meta tags, sometimes with PHP, and soon. Soon, we ended up with a list of 600,000 artist bios with double- or triple encoded information, with data being stored in different ways depending on who programmed the feature or implemented the patch. A classical technical rat’s nest.Indeed, navigating through UTF-8 related data encoding issues can be a frustrating and hair-pul...
Post a Comment
Read more

How To Create Shortcodes In WordPress

We can create own shortcode by using its predified hooks add_shortcode( 'hello-world', 'techsudhir_hello_world_shortcode' ); 1. Write the Shortcode Function Write a function with a unique name, which will execute the code you’d like the shortcode to trigger: function techsudhir_hello_world_shortcode() {    return 'Hello world!'; } Example: [hello-world] If we were to use this function normally, it would return Hello world! as a string 2. Shortcode function with parameters function techsudhir_hello_world_shortcode( $atts ) {    $a = shortcode_atts( array(       'name' => 'world'    ), $atts );    return 'Hello ' . $a['name'] . !'; } Example: [hello-world name="Sudhir"] You can also call shortcode function in PHP using do_shortcode function Example: do_shortcode('[hello-world]');
Post a Comment
Read more

Integrating Kafka with Node.js

Integrating Kafka with Node.js Apache Kafka is a popular open-source distributed event streaming platform that uses publish & subscribe mechanism to stream the records(data). Kafka Terminologies Distributed system: Distributed system is a computing environment where various software components located on different machines (over multiple locations). All components coordinate together to get stuff done as one unit.   Kafka Broker: Brokers are cluster of multiple servers. Message of each topic are split among the various brokers. Brokers handle all requests from clients to write and read events. A Kafka cluster is simply a collection of one or more Kafka brokers. Topics: A topic is a stream of "related" messages. Its unique throughout application. Kafka producers write messages to topics. Producer: Producer publishes data on the topics. A producer sends a message to a broker and the broker receives and stores messages. Consumers: Consumers read data from topics. A consu...
Post a Comment
Read more