Slooooooow UPDATE query with MySQL 5.5
I am using a local MySQL server a lot to handle, prepare and restructure big research tables. Ubuntu Precise uses MySQL server 5.5 while the previous distros used 5.1. I thought that might be good until I tried to import a table with some dozen variables and some thousand rows with and UPDATE statement which took some seconds (10 min!!).
nick rulez on forum.mysql.com quantified this fact and revealed that the default database engine changed from “MyISAM” to “InnoDB” and that indeed InnoDB is considerably slower in this regard.
So I want MyISAM back.
To list the available and default engines:
show engines
which produces
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
Setting the database engine
When you create a new table, you can specify which storage engine to use by adding an ENGINE table option to the CREATE TABLE statement:
CREATE TABLE t (i INT) ENGINE = INNODB;If you omit the ENGINE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the –default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.
You can set the default storage engine to be used during the current session by setting the storage_engine variable:
SET storage_engine=MYISAM;[…]
To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:
ALTER TABLE t ENGINE = MYISAM;
I want MyISAM all the time so I decided for the my.cnf option. But where is my.cnf? According to debianadmin:
sudo nano /etc/mysql/my.cnf
Now append
default-storage-engine = MyISAM
Safe and exit with Ctrl-o, Ctrl-x and restart the server.
sudo restart mysql
and MyISAM it is.
Rforge 