top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is difference between MyISAM and InnoDB storage engines in MySQL?

+1 vote
508 views
What is difference between MyISAM and InnoDB storage engines in MySQL?
posted Jun 9, 2014 by Rahul Mahajan

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

Much more conservative approach to disk space management – each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

answer Jun 10, 2014 by Vrije Mani Upadhyay
0 votes

1 : InnoDB provides us row level locking while MyISAM provides us table level locking.

2 : InnoDB offers foreign key constraints whereas in MyISAM does not have foreign key constraints.

3 : InnoDB does not have full text search whereas MyISAM provides us full text search.

answer Jun 10, 2014 by Mohit Sharma
Similar Questions
0 votes
munin MyISAM & InnoDB issues

I have 4 out of 6 mysql graphs working in munin. "MySQL isam/myisam table-space usage" is a broken image and "MySQL InnoDB free tablespace" says:

"This service is in CRITICAL state because one of the values reported is outside the allowed range.
Field Internal name Type Warn Crit Info Bytes free free gauge **********: **********:"

I don't know enough about mysql (or munin) to figure out what's going on. I'm using both MyISAM and InnoDB tables. Can anyone help me out?

+1 vote
Optimizing InnoDB tables in MySQL?

I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables.

SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND Data_free > 0

After that, I have seen that there are 49 fragmented tables. With one table, I have executed "optimize table table_name;" and "analyze table table_name;". The result is the same, the table continuous fragmented.

Any suggestions? I have followed the mysqltuner recommendations...

0 votes
UPDATE_TIME for InnoDB in MySQL 5.7

The MySQL 5.7 changelog mentions:
"Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache."

This is great news! However, I would in fact need the UPDATE_TIME to persist across database server resets. Is this feature being considered or discussed? Where might I find it online?

+1 vote
Mysql Innodb Performance - How to minimise multicolumn index?

Below table contains billion of rows,

CREATE TABLE `Sample1` (
  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` varchar(45) NOT NULL,
  `c3` tinyint(4) DEFAULT NULL,
  `c4` tinyint(4) DEFAULT NULL,
  `time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `varchar_time_idx` (`c2`,`Time`),
  KEY `varchar_c3_time_idx` (`c2`,`c3`,`Time`),
  KEY `varchar_c4_time_idx` (`c2`,`c4`,`Time`),
  KEY `varchar_c3_c4_time_idx` (`c2`,'c3', `c4`,`Time`),
) ENGINE=InnoDB AUTO_INCREMENT=10093495 DEFAULT CHARSET=utf8

Four multi column index created because having below conditions in where

1) c2 and time
2) c2 and c3 and time
3) c2 and c4 and time
4) c2 and c3 and c4 and time

Cardinality wise c2, c3 and c4 are very low. (ex: Out of one million c2, c3 and c4 have 50 unique column in each)

Column time contains mostly unique fields.

Select, insert and update happened frequently.

Tables has 5 indexing fields(4 multi column). Due to this, 1) Insert and update on index fields become costlier. 2) As the table keep on growing (Nearly one billion rows), Index size also increase more rapidly.

Kindly suggest good approach in mysql to solve this use case.

...