Mysql: Should I always prefer MySQL InnoDB over MyISAM?

Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits?

 Answers

MyISAM is transactionless and heap-organized. The records are identified by the row offset in the table and the indexes store this offset as a row pointer.

InnoDB supports transactions and is index-organized. The records are identified by the value of the PRIMARY KEY (or a hidden internal column is there is no PRIMARY KEY defined) and are stored in a B-Tree. The secondary indexes store the value of the PRIMARY KEY as a row pointer.

Queries that involve full table scans or secondary index lookups are usually faster on MyISAM tables.

Queries that involve PRIMARY KEY seeks are usually faster on InnoDB tables.

MyISAM tables store the number of records in the table in the table's metadata, that's why the queries like this:

SELECT  COUNT(*)
FROM    myisamtable

are instant.

MyISAM tables are completely locked on the DML operations (with several exceptions).

InnoDB tables lock individual records and index gaps, however these are the records and the gaps that are scanned, not only those matched by the WHEREcondition. This can lead to the records being locked despite the fact they don't match.

InnoDB tables support referential integrity (FOREIGN KEYs) . MyISAM tables don't.

There are several scenarios that can show benefits of both engines.

---

To put it simply:

You should use InnoDB:

• if you need transaction support
• if you need foreign keys

You should use MyISAM:

• if you don't need the above AND
• you need speed (faster database operations)

---

InnoDB is a fully ACID compliant database engine and therefore offers support for transactions, etc. As such, it can therefore be slower than the MyISAM database which tends to be optimised in a different direction.

Therefore if you need transactions InnoDB (or another RDBMS such as PostgreSQL) is the obvious choice.

There's a reasonable comparison over on Wikipedia

Mysql: Renaming an InnoDB table without updating foreign key references to it?

I am trying to replace an InnoDB table with a new table, and I want all foreign key references that point to the old table to point to the new table.

So I tried this:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

Unfortunately, even with foreign_key_checks disabled, all references pointing to foo are changed to point to foo_old. Now I am looking for either

• a way to change the foreign key references back without rebuilding the entire table, OR
• a way to rename a table without updating foreign key references.

I tried dropping the foreign keys and recreating them, but since the tables are huge, it takes hours. The whole point of replacing the table was to make a schema change with limited downtime.

 Answers

Old questions, but following is a possible way around. Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Make sure you run it as one query so the foreign_key_checks applies to the whole thing. Hope this helps.

---

On MySQL 5.6 with innodb_file_per_table=ON allows you to swap the table spaces on the fly. This can't be done completely using SQL as file operations need to be performed separately. First prepare the foo_new table to be copied and drop the foo data:

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

At this point you need to copy the relevant InnoDB files to correct name. Files are stored in your data directory. On Debian, for example, they are by default in /var/lib/mysql/yourdatabase and files are foo_new.ibd, foo_new.cfg and foo_new.frm. Copy them to foo.ibd, foo.cfg and foo.frm, respectively. For example:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Pay attention that MySQL has access to the new files (e.g. they have correct owner, access rights). Once done you can import the table again and enable foreign keys:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

This only copies foo_new to foo. Repeat the steps if you need to copy foo to foo_old.

---

I found a way around this... you just drop the source table instead of renaming it.

For this example, we'll call the table 'mytbl'.

1. create copy of the source table, e.g. 'mytbl_new'
2. copy data into the new table
3. drop the source table 'mytbl'
4. rename 'mytbl_new' to 'mytbl'

The only downside is you can't keep a backup of your original table, but you could mysqldump it before hand. Alternatively, you could create an additional table copy if you wanted a verbatim copy of the original table.

Mysql: How to disable index in innodb

I'm trying to speed up bulk insert in an InnoDB table by temporary disabling its indexes:

ALTER TABLE mytable DISABLE KEYS;

But it gives a warning:

+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytable' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

How can we disable the indexes?

What alternatives are there to avoid using the index when doing bulk inserts?

How can we speed up the process?

 Answers

Have you tried the following?

    SET autocommit=0; 
    SET unique_checks=0; 
    SET foreign_key_checks=0;

From the MySQL References https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

See Section "Bulk Data Loading Tips"

---

to reduce the costs for re-calculating the indexes you should insert the data either using DATA INFILE or using Mysql Multi Row Inserts, like

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

-> so inserting several rows with one statement.

How many rows one can insert with one statement depends on the max_allowed_packet mysql setting.

Mysql: How do I know if a mysql table is using myISAM or InnoDB Engine?

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables. However, you can specify a storage engine to be used during one session with:

SET storage_engine=InnoDB;

So you don't have to specify it for each table.

How do I confirm, if indeed all the tables are using InnoDB?

 Answers

If you use SHOW CREATE TABLE, you have to parse the engine out of the query.

Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely).

The correct query to use is SHOW TABLE STATUS - you can get information on all the tables in a database:

SHOW TABLE STATUS FROM `database`;

Or for a specific table:

SHOW TABLE STATUS FROM `database` LIKE 'tablename';

One of the columns you will get back is Engine.

---

show create table <table> should do the trick.