MyISAM is a non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. Also, is the default storage engine type for version prior to 5.5.
50
votes
8answers
23k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
3
votes
3answers
3k 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?
8
votes
3answers
17k 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?
4
votes
2answers
3k 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, ...
4
votes
4answers
1k 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 ...
5
votes
2answers
1k 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 ...
3
votes
1answer
345 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
6
votes
1answer
2k 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 ...
4
votes
1answer
310 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, ...
9
votes
2answers
2k 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 ...
3
votes
1answer
293 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 ...
3
votes
2answers
529 views
Quick MySQL Backup
I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack.
Or mysqldump will work ...
3
votes
3answers
333 views
Dynamically changeable database
We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
5
votes
1answer
349 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?
3
votes
3answers
563 views
InnoDB vs MyISAM with many indexes
I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a ...
1
vote
1answer
732 views
importing myisam 5.0 database into a 5.5 innodb server
I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
6
votes
2answers
1k 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 ...
4
votes
2answers
276 views
Less RAM than Index_length MyISAM
I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
2
votes
2answers
589 views
How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast.
The problem is, from research, MyISAM is okay with being ...
1
vote
2answers
300 views
Any problem will all InnoDB and one MyISAM table?
I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
9
votes
1answer
841 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 ...
5
votes
2answers
390 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.
...
4
votes
3answers
596 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 ...
1
vote
2answers
2k views
Set up a MySQL slave using mysqldump to get the initial data
I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
4
votes
2answers
4k 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 ...
2
votes
2answers
308 views
Which first: upgrade mysql version or convert storage engine?
I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets.
I am planning on:
upgrading to 5.5
converting ...
1
vote
2answers
1k views
Table compression in InnoDB?
I have read that table index compression happens just in MyISAM, but I came across
an article in dev.mysql that said: InnoDB supports table and index compression!
So now what is the pros of MyISAM?
...
3
votes
2answers
122 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
3
votes
2answers
1k views
Performance settings for MyISAM tables - keep everything in memory
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage ...
2
votes
2answers
3k 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 ...
1
vote
1answer
169 views
MySQL my.cnf Optimization
We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance.
We have tried to configure my.cnf but heavy queries got ...
1
vote
5answers
4k 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 ...
3
votes
1answer
1k views
Is Percona xtrabackup the right choice for my 500GB MySQL backup?
I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
3
votes
2answers
309 views
Why innodb doesn't store geospatial data?
I am very curious.
I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam.
But why would I do so?
Why can't innodb store ...
3
votes
1answer
440 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 ...
2
votes
2answers
59 views
Are there implicit indexes in InnoDB like MyISAM?
If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:
(a)
(a, b)
(a, b, c, id)
Is this valid for ...
2
votes
1answer
986 views
mysql table very large size although it doesn't have a lot of records
I have drupal website, one of the tables is cache_form. I have a cron to
DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW());
which works fine.
The size of cache_form.myd is 4.9G ...
2
votes
1answer
852 views
MySQL Config for Large MyISAM tables
I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get ...
1
vote
2answers
109 views
MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)
I'm going to be converting this table to InnoDB in a production environment.
The DB is on a cloud server with ...
1
vote
1answer
1k views
MySQL MyISAM Locking question
It was my understanding that MyISAM uses table locking during inserts and updates.
To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
0
votes
1answer
113 views
How to interpret explain sql?
SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.293413 * SQRT( pow( - 6.185 - TB.Latitude, 2 ) + pow( 106.773 - TB.Longitude, 2 ) * 0.988392289802 ) AS Distance
FROM `tablebusiness` AS TB
...
