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.
512
questions
282
votes
10
answers
286k
views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
- 3,049
117
votes
5
answers
239k
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.5k
64
votes
4
answers
141k
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?
- 2,009
55
votes
3
answers
73k
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,541
53
votes
6
answers
153k
views
How to update 10 million+ rows in MySQL single table as Fast as possible?
Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64.
I have ...
user16108
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
38
votes
5
answers
58k
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) ...
- 563
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
31
votes
6
answers
57k
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 ...
- 937
25
votes
3
answers
19k
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.5k
24
votes
6
answers
44k
views
DELETE command not completing on 30,000,000 row table
I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. ...
- 551
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 ...
- 405
22
votes
1
answer
22k
views
Does mysqldump export indices, by default?
I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:
--disable-keys, -K
which suggests, ...
- 377
18
votes
2
answers
77k
views
MySQL performance problem using indexed datetime column
I tried to solve the following problem for about one hour now and still didn't get any further with it.
Okay, I have a table (MyISAM):
+---------+-------------+------+-----+-------------------+-----...
- 283
16
votes
5
answers
107k
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 ...
16
votes
3
answers
27k
views
Will OPTIMIZE TABLE have any impact on my data?
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.
But before doing so, I would (of course) like to know if ...
16
votes
2
answers
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 ...
- 261
16
votes
2
answers
38k
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 ...
- 163
16
votes
3
answers
19k
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
15
votes
1
answer
14k
views
MySQL command querying all MyISAM database
I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
- 263
15
votes
4
answers
29k
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 ...
- 531
13
votes
2
answers
16k
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 ...
- 233
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.5k
12
votes
1
answer
16k
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 '%...
- 647
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, ...
- 495
11
votes
6
answers
35k
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 ...
- 814
11
votes
4
answers
41k
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 ...
- 151
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
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 ...
- 652
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
10
votes
7
answers
37k
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.
...
- 571
10
votes
2
answers
70k
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 ...
- 647
10
votes
2
answers
10k
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 ...
- 799
10
votes
3
answers
36k
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 ...
- 415
10
votes
1
answer
3k
views
recover a single mysql database on a busy master-slave replicated system
Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.
I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
- 267
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
9
votes
4
answers
31k
views
Why does MySQL produce so many temporary MYD files?
On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD.
For example today :
[2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD :...
plegall
9
votes
3
answers
14k
views
Inserts in MySQL 8 are slower than Inserts in MySQL 5.7
I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "...
9
votes
1
answer
7k
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 ...
- 989
9
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):...
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
8
votes
3
answers
9k
views
How much memory will a MEMORY table take up?
Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY ...
- 652
8
votes
2
answers
1k
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 ...
- 495
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,543
8
votes
1
answer
11k
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 ...
- 2,009
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
8
votes
1
answer
73k
views
What is causing Waiting for table level lock errors?
We got the database hanging twice already and trying to find a cause.
show processlist
Waiting for global read lock | INSERT INTO {myisam_table} ...
In here the disk space was full so we thought ...
- 181
8
votes
1
answer
9k
views
Setting up replication for MyISAM tables?
Before setting up a replication within MySQL, the data first needs to be transferred to the slave.
Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, ...
- 619
8
votes
2
answers
36k
views
Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement
I am working on a schema for an analytics system which tracks usage times, and there is a need to see total usage time in a certain date range.
To give a simple example, this type of query would be ...
- 183
8
votes
3
answers
6k
views
Occasionally simple queries are slow with InnoDB caused by slow query end [closed]
We are having serious issues with one of our productions servers. The setup in question is CentOS 7 64-bit server with 16 GB of ram and 250GB SSD (so the setup should be very fast). The LEMP stack is ...
