difference
between MyISAM and InnoDB.
The main
differences between InnoDB and MyISAM are support for "referential
integrity" and "transactions".
If you
need the database to enforce foreign key
constraints, or you need the database to support then you would choose the
InnoDB engine, since these features are absent from the MyISAM engine.
Those are
the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive
lock on the table, and while that lock is held, no other session can perform a
SELECT or a DML operation on the table.
Those two
specific engines you asked about (InnoDB and MyISAM) have different design
goals. MySQL also has other storage engines, with their own design goals.
So, in
choosing between InnoDB and MyISAM, the first step is in determining if you need
the features provided by InnoDB. If not, then MyISAM is up for consideration.
A more
detailed discussion of differences is rather impractical (in this forum) absent
a more detailed discussion of the problem space... how the application will use
the database, how many tables, size of the tables, the transaction load,
volumes of select, insert, updates, concurrency requirements, replication
features, etc.
MYISAM:
1.
MYISAM supports
Table-level Locking
2.
MyISAM designed for need
of speed
3.
MyISAM does not support
foreign keys hence we call MySQL with MYISAM is DBMS
4.
MyISAM stores its
tables, data and indexes in diskspace using separate three different files.
(tablename.FRM, tablename.MYD, tablename.MYI)
5.
MYISAM not supports
transaction. You cannot commit and rollback with MYISAM. Once you issue a
command it’s done.
6.
MYISAM supports fulltext
search
7.
You can use MyISAM, if
the table is more static with lots of select and less update and delete.
INNODB:
1.
InnoDB supports
Row-level Locking
2.
InnoDB designed for
maximum performance when processing high volume of data
3.
InnoDB support foreign
keys hence we call MySQL with InnoDB is RDBMS
4.
InnoDB stores its tables
and indexes in a tablespace
5.
InnoDB supports
transaction. You can commit and rollback with InnoDB
==================================================================
How to convert all tables from MyISAM into InnoDB?
<?php
// connectivity with your database here first
//-------------------------------------------------
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND ENGINE <> 'InnoDB'";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE $tbl ENGINE=INNODB";
mysql_query($sql);
}
?>
