MyISAM is the non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. In addition, it is the default storage engine type for versions of MySQL prior to 5.5.

learn more… | top users | synonyms

112
votes
9answers
99k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
40
votes
4answers
74k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
19
votes
3answers
11k views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
19
votes
3answers
24k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
17
votes
3answers
9k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
14
votes
2answers
4k views

Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down. Now that the ...
12
votes
3answers
11k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
11
votes
3answers
1k views

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
11
votes
1answer
5k views

MySQL table with 100,000 records queried often

I have a single database of about 100 tables to store various kinds of information. The most important table is our order table which is used to store customers orders and is over 100000 records as ...
10
votes
2answers
21k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
8
votes
2answers
444 views

Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
8
votes
3answers
14k views

MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done. I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
8
votes
2answers
4k views

Any gotchas at all with converting from MyISAM to InnoDB?

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
8
votes
2answers
3k views

BigInt or Varchar for a large number I won't ever sort / calculate on

I need to store users' Facebook IDs in a column in my DB. The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do ...
8
votes
1answer
219 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
7
votes
4answers
5k views

Benefits of table level locking

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table. Edit Its ...
7
votes
2answers
3k views

How to drop empty tables

Is it possible to drop all empty tables from my huge database (mysql)? I'm looking for a sql command to automatically remove all those empty tables. Currently, I have 305 tables in my dataset, and ...
7
votes
2answers
15k views

Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four. I am doing expensive joins on large tables, ...
7
votes
1answer
6k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
7
votes
1answer
2k views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
7
votes
2answers
18k views

Why do MySQL tables crash? How do I prevent it?

I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable. Luckily, a simple REPAIR TABLE mdl_user made it work back again. The thing is that I don't know why ...
7
votes
1answer
5k views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
7
votes
2answers
38k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
7
votes
1answer
5k views

MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`

I know very little about database administration but I have to deal with some very large tables on my site. This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
7
votes
1answer
797 views

LOAD DATA INFILE blocks on concurrent use against MyISAM tables

I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks &...
6
votes
1answer
7k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
6
votes
1answer
7k views

Why full-text-search returns less rows than LIKE

I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists. Example statements: SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE '%...
6
votes
1answer
696 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL 5.5.35-...
6
votes
3answers
431 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=...
6
votes
4answers
18k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
6
votes
1answer
2k views

How much does table size affect performance?

Lets say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often. By not regularly archiving or deleting these ...
6
votes
2answers
2k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
5
votes
4answers
4k views

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. ...
5
votes
5answers
18k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
5
votes
3answers
938 views

How can I select using the same fields from an undetermined number of tables using MySQL?

I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this: foo_reference1 foo_reference2 foo_referencea foo_referenceb .... ...
5
votes
1answer
854 views

Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
5
votes
4answers
8k views

mysql insert into indexed table taking long time after few million records

I have a table like this CREATE TABLE IF NOT EXISTS `dnddata` ( `numbers` varchar(10) NOT NULL, `opstype` char(1) NOT NULL, PRIMARY KEY (`numbers`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!...
5
votes
1answer
5k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
5
votes
1answer
278 views

Which MySQL storage engine is the most similar to ARCHIVE when that is not available?

I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
5
votes
1answer
2k views

Which mysql storage engine to choose?

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB). The theories say: Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
5
votes
2answers
3k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE `...
5
votes
2answers
1k views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
5
votes
2answers
708 views

MySQL fulltext selection performance

I have 1.5M rows in a table. Following is the table create code: CREATE TABLE `jobs` ( `id` INT(8) NOT NULL AUTO_INCREMENT, `job_id` VARCHAR(50) NOT NULL DEFAULT '', `title` VARCHAR(255) ...
5
votes
1answer
978 views

Quick MySQL Backup (1 file per table)

I asked a relevant question few months back and got answer with wide variety of options for backup. But this time I have different scenario and I have got a database with 7 tables and 1 of them has ...
5
votes
2answers
291 views

Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Followings are what I need to do on my project: 1- If a search term is new and unique, then add the search term 2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
4
votes
2answers
2k views

Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for punctual,...
4
votes
3answers
3k views

MySQL tables/databases into different disks

I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks. Lets say you have 5 tables/...
4
votes
2answers
3k views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
4
votes
2answers
724 views

Should I move to InnoDB during a planned migration?

I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
4
votes
5answers
16k views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...