InnoDB 1.1
combines more reliability and performance with usability enhancement compare to
InnoDB 1.0.
Since MySQL
5.5, InnoDB is the default storage engine, instead of MyISAM.
CREATE
TABLE TABLENAME statement without an ENGINE= clause creates an InnoDB table.
|
•
|
Its
supports DML operations and follow the ACID property and with can do commit
and rollback for long transactions and supports for crash-recovery to protect
user data.
|
|
•
|
Its
supprts Row-level locking and Oracle-style consistent reads to increase
multi-user concurrency and performance.
|
|
•
|
InnoDB
tables stores your data on disk to optimize queries based on primary keys.
|
|
•
|
InnoDB
table has a primary key index called the clustered index to organizes the
data to minimize I/O.
|
|
•
|
InnoDB
also supports FOREIGN KEY constraints to remove inconsistencies across
different tables.
|
|
•
|
You can
freely mix InnoDB tables with tables from other MySQL storage engines. We can
join data from InnoDB and MEMORY tables in a single query.
|
|
•
|
It is
designed for CPU efficiency and maximum performance when processing large
data.
|
|
•
|
It use
B-tree indexes only. Not supports for T-tree indexes & Hash indexes.
|
|
•
|
It can
store up to 64TB in a table and supports row level locking.
|
|
•
|
It can
store Compressed data and Encrypted data along with Replication support and
Foreign key support.
|
|
•
|
It uses
Data caches and Index caches while storing data.
|
As compare
to InnoDB, MyISAM tables have characteristics are as under:
|
•
|
MYISAM tables are stored with the low byte first, which makes
the data machine and operating system are as independent. Due to that widely
used in mainstream machines.
|
|
•
|
All numeric key values are stored with the high byte first for
better index compression.
|
|
•
|
It can store (232)2 rows in
a MyISAM table and maximum number of indexes per MyISAM table is 64 and max
16 columns can have an index. It stores maximum key length is 1000 bytes,
which can be changed by changing the source and recompiling table to default
size upto 1024 bytes.
|
|
•
|
It improves space utilization in index tree by rows are inserted
in sorted order when you are using AUTO_INCREMENT column in index tree by
splitting in a manner that only high node only contains one key.
|
|
•
|
It supports Dynamic-sized rows, due to that it is much less
fragmented when mixing deletes with updates and inserts, automatically by
combining adjacent deleted blocks and by extending blocks if the next block
is deleted.
|
|
•
|
MyISAM supports concurrent inserts, If a table has no free
blocks in the middle of the data file, you can INSERT new rows.
|
|
•
|
We can store the data file and index file in different
directories on different physical devices to get more speed with the DATA
DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE.
|
|
•
|
Large files - up to 63-bit file length and 256 TB in size to
support large files.
|
|
•
|
BLOB and TEXT columns can be indexed, NULL values can inserted
in indexed columns, which takes 0 to 1 bytes per key.
|
|
•
|
Each character column can have a different character set.
|
|
•
|
It Support for a true VARCHAR, a VARCHAR column starts with a
length stored in one or two bytes. MYISAM Tables with VARCHAR columns may
have fixed or dynamic row length.
|
|
•
|
It does not supports row level locking and transactions.
|
|
•
|
It use B-tree indexes only. Not supports for T-tree indexes
& Hash indexes.
|
|
•
|
It can store Compressed data and Encrypted data along with
Replication support, but not for Foreign key support.
|
|
•
|
It use B-tree indexes only. Not supports for T-tree indexes and
Hash indexes.
|
|
•
|
It supports Full-text search indexes and not for the Clustered
indexes and Hash indexes.
|
|
•
|
It uses Index caches while storing data but not uses Data
caches.
|
