Questions tagged [myisam]
70 questions
17
votes
5 answers
What is the difference between InnoDB and MyISAM?
I am using MySQL as my database for my current web project. I am new to MySQL. Please explain to me the difference between InnoDB and MyISAM.
user16614
16
votes
5 answers
How do you choose a MySQL database engine
In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys).
Does it always come down to trying both and measuring? Or are there good rules of thumb regarding the number…
![](../../users/profiles/212.webp)
Tony Meyer
- 889
- 1
- 13
- 25
15
votes
3 answers
MySQL Replication Performance
I'm having a serious issue with MySQL 5.5 replication performance between two machines, mostly myISAM tables with statement based replication. The binary logs and mysql data directory are both located on the same Fusion ioDrive.
The problem was a…
![](../../users/profiles/128665.webp)
Nick
- 153
- 1
- 6
15
votes
4 answers
How to know storage engine used of a database?
Previously, on every database created, I use:
mysql -u root -p
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL ON dbname.* TO 'dbuser'@'localhost';
and then use the database without thinking about MyISAM or InnoDB
How to know…
![](../../users/profiles/73223.webp)
cewebugil
- 705
- 3
- 8
- 12
13
votes
4 answers
Simpler way to convert all tables from InnoDB to MyISAM
Previously, I use this:
USE dbname;
ALTER TABLE tablename ENGINE=MYISAM;
I'm looking for simpler way to convert all tables in a database, rather than writing every table name one by one
![](../../users/profiles/73223.webp)
cewebugil
- 705
- 3
- 8
- 12
10
votes
5 answers
MyISAM for data reads
I have a table with around 1 Billion rows, and its 98% read intensive.
I tried tuning the database, with different storage engines (MyISAM and InnoDB)
Then ran a few test to view the performance
In the where clause I had a primary key ID, and it…
![](../../users/profiles/72459.webp)
Akash
- 229
- 3
- 7
9
votes
3 answers
Do InnoDB tables got locked during mysqldump when mixed with MyISAM?
I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following:
MySQL servers
they are not replicated
each server stands for its own
This number can grow, so setting up a master/slave…
![](../../users/profiles/127965.webp)
tounano
- 211
- 2
- 7
8
votes
2 answers
How to log all the MySQL queries that waited for a table lock?
We are trying to diagnose a deadlock problem MySQL 5.0. Currently all the tables are MyISAM tables and so all the locks are table locks. We have a good table_locks_waited to table_locks_immediate ratio (lower than 1:1000), but within a couple of…
![](../../users/profiles/32430.webp)
netvope
- 2,113
- 5
- 25
- 35
7
votes
2 answers
How do MyISAM and InnoDB Utilize HD Space?
My MySQL server is running out of HD space fast. Most of my larger tables use the InnoDB engine (for no mission-critical reason). In an effort to avoid the dreaded 'drop database to recover innodb disk-space' response, I'd like to better understand…
![](../../users/profiles/15814.webp)
Mike B
- 213
- 1
- 3
- 7
6
votes
1 answer
HA Setup for MySQL/mostly MyISAM Tables
I am currently using Master-Slave Replication, separating reads from writes at the application level. The only kind of fail-over currently implemented is application level detection of master failure, making the slave the new master so the…
![](../../users/profiles/107668.webp)
Niko S P
- 1,182
- 8
- 15
6
votes
2 answers
MySQL 5.0 -> 5.1 upgrade, table upgrades taking a very long time
We have a database server that was running MySQL 5.0 on Debian etch (!), and decided it was time to upgrade. It is now running 5.1 on Debian squeeze.
This database server has about 1.2TB of MyISAM data on a SATA RAID array, and 2GB of ram. Normally…
![](../../users/profiles/78116.webp)
Alex Forbes
- 2,392
- 2
- 19
- 26
5
votes
5 answers
phpMyAdmin showing database is MyISAM but tables are InnoDB
Is it possible for a database to be of one type (MyISAM) and the tables to be of another type (InnoDB) or is this a bug in phpMyAdmin?
It is showing exactly that.
Picture Here…
![](../../users/profiles/15088.webp)
Brian Armstrong
- 1,557
- 3
- 18
- 22
5
votes
2 answers
Why is MySQL is using so many temporary tables?
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 created on disk
table_open_cache = 125…
![](../../users/profiles/113505.webp)
ananthan
- 1,490
- 1
- 17
- 27
5
votes
2 answers
MySQL InnoDB table shows a negative number of rows in phpMyAdmin
I just converted a MyISAM table to InnoDB with around 1.4 million rows. When I converted it to InnoDB, it now shows -1.4 million rows. The table still works as expected, but why does it shows negative in the rows column?
![](../../users/profiles/8964.webp)
James Simpson
- 1,601
- 2
- 14
- 30
4
votes
3 answers
mysql performance tuning, write, get db into ram
I am trying to improve the performance of a website by optimizing the Mysql server. In addition to general optimization I have a feeling that some write operations take an unacceptably long time.
The server is a Mac os x server 10.5.8 (I think this…
![](../../users/profiles/99725.webp)
tomsv
- 273
- 3
- 8