Questions tagged [myisam]
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.
143
questions
280
votes
10
answers
284k
views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
- 3,029
117
votes
5
answers
231k
views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
- 23.3k
55
votes
3
answers
72k
views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
- 4,479
63
votes
4
answers
138k
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?
- 1,989
16
votes
3
answers
18k
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?
- 1,271
24
votes
3
answers
11k
views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
- 395
11
votes
2
answers
22k
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, ...
- 307
5
votes
1
answer
11k
views
Mysql fulltext search my.cnf optimization
I have open a question on https://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here.
We built a news site. Every day we will input tens of ...
- 165
6
votes
2
answers
3k
views
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
I have a basic table :
create table fullTextTest
(
id INT(11) NOT NULL,
superText CHAR(255) NOT NULL,
superLongText TEXT NOT NULL,
primary key (`id`),
FULLTEXT KEY `superText` (`...
- 161
7
votes
1
answer
7k
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 ...
- 632
7
votes
4
answers
8k
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 ...
- 1,271
32
votes
3
answers
21k
views
How Does Table Partitioning Help?
I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
- 1,271
8
votes
1
answer
1k
views
Does MySQL still handle indexes in this way?
Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.
If a ...
- 869
11
votes
2
answers
7k
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 ...
- 632
9
votes
3
answers
35k
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 ...
- 405
8
votes
1
answer
3k
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?
- 8,281
5
votes
1
answer
6k
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 ...
- 959
8
votes
2
answers
4k
views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
- 183
6
votes
2
answers
2k
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.
...
user12940
5
votes
1
answer
1k
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, ...
- 163
4
votes
4
answers
16k
views
Optimized my.cnf for high-end and busy server
Currently my main culprit of poor performance is MySQL and I'm not DB savvy. So far, I've had little luck in optimizing MySQL configs on my server, which has a Sandy Bridge processor with 16GB RAM and ...
- 2,934
2
votes
2
answers
2k
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 ...
40
votes
4
answers
17k
views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
Alex
13
votes
5
answers
5k
views
Is it unwise to run replication on same physical server?
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
- 23.3k
9
votes
2
answers
8k
views
Why is MySQL not using the index with the higher cardinality?
I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
- 197
8
votes
3
answers
19k
views
InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)
This server is a virtual machine running CentOS 6.0, MySQL 5.5.21
There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 (HY000):...
6
votes
3
answers
8k
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 ...
- 203
6
votes
1
answer
4k
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 ...
- 1,989
4
votes
1
answer
2k
views
Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?
I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
- 41
4
votes
2
answers
1k
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 ...
- 654
3
votes
4
answers
8k
views
Can I specify a different disk for a specific MySQL table?
I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is ...
- 789
3
votes
3
answers
843
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 ...
- 1,007
3
votes
4
answers
3k
views
Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that ...
- 1,175
3
votes
2
answers
2k
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 ...
- 443
2
votes
1
answer
477
views
optimal table design mysql with primay key and varchar value
I have a table:
id (PRIMARY KEY AUTO_INCREMENT)
value (UNIQUE KEY)
which currently holds over 50 millions rows (and growing fast) and is hit hard:
process 1 queries this table (>200/sec) with a ...
Martin
25
votes
3
answers
18k
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 ...
- 23.3k
12
votes
3
answers
4k
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, ...
- 485
6
votes
2
answers
22k
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 ...
- 2,582
5
votes
3
answers
6k
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/...
- 741
5
votes
2
answers
2k
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 ...
- 151
3
votes
2
answers
20k
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,606
2
votes
1
answer
136
views
How to go about modeling the main object of relationship?
I'm using MySQL and MyISAM tables.
I have a bunch of objects that I want to group together
1 object belongs to 1 and only 1 group
1 group can have many objects
1 group has 1 required main object ...
- 189
12
votes
1
answer
15k
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 '%...
- 637
11
votes
1
answer
11k
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 ...
- 111
7
votes
2
answers
3k
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 ...
- 256
5
votes
2
answers
3k
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,...
- 205
5
votes
1
answer
3k
views
How does mysql deal with queries that touches myisam and innodb tables?
In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS
If a query involves both myisam and innodb tables, will the query use table lock or row ...
- 611
3
votes
1
answer
10k
views
How can I optimize my mysql setup to create my index faster?
I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored.
I have an MyISAM like ...
- 239
2
votes
1
answer
3k
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 ...
2
votes
2
answers
1k
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 ...
- 21