myisam_vs_innodb_fitnessnit_com

Brief description on MYSQL MYISAM and INNODB Engines

Posted on Posted in PHP, Technology


MYISAM :

Myisam was the default storage engine for MYSQL relational database management system version upto 5.5. It is based on the older (and no longer available) ISAM storage engine but has many useful extensions. ISAM(Indexed Sequential Access Method) a method for indexing data for fast fetching.

File System : Each MYISAM table used three files to store data on disk. Each file have its uniqueness by its name. File names begin with the table name and an extension to indicate the file type. The ‘.frm’ extension file used to store the table format or structure of the table . The ‘.MYD’ (My data) extension file used to store the data of the table. The ‘.MYI’ (My Index) extension file used to store the indexes of the table.

MYISAM is used where reading or fetching data queries are more from insert or update. Where insets/updates are used when database is not in use or in night time.

Indexing : Because MYISAM data indexing feature provide a very fast performance for fetching data. MyISAM supports FULLTEXT indexing.
The indexing is working like if you trigger a query for fetching results like :

Select * from table_name where id < 100

Now if your database has 500 rows, then the indexed pointer go to the half of the records means at (id = 250), then it checks your condition if its current position is greater than your condition (in our case its 100) or not. Then in its second trial the pointer goes to the half of its current position means (id = 125), then again it compare its position with our query condition, and so on, By the use of indexing it reaches at the desired record in just 3-4 hits instead of start searching the record from 1st row to 100th row.

MYSQL support table level locking for MYISAM, it means that only one operation is processed for one table at a time. That table is locked for other users until the process execution.



INNODB :

INNODB is a high performance and reliable storage engine for MYSQL. INNODB is the default storage engine for MYSQL RDBMS version greater than 5.5. INNODB tables arrange your data on disk to optimize common queries based on primary keys. its design follows the ACID model.

ACID an acronym for atomicity, consistency, isolation and durablity. The transactional behavior of INNODB adhere to the ACID Principle. Transactions can be committed or rolled back. 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. The database remains in a consistent state at all times, after each commit or rollback, and while transactions are in progress. 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.

INNODB uses Row level Locking, it means that if an insert, update or delete query is working on a row, only that row is locked for users until the process execution instead of full table lock.
To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
If your application have more insert, update queries instead of select query, you application perform faster with INNODB.

Hope this article helps to answer all your queries regarding the MYISAM and INNODB Engines , you can also ask your questions on this topic by commenting below.