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

3
votes
0answers
490 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
2
votes
4answers
11k 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 ...
2
votes
1answer
462 views

Best way to add a new column to a large table? (MySQL - MyISAM)

Here's more info on the table that needs to be altered: 15GB size MyISAM table with Index around 5GB Over 100 million rows MySQL 5.1 on RHEL 5 with 4GB RAM Currently live online What's the best ...
2
votes
1answer
3k 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 ...
2
votes
3answers
609 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
2
votes
1answer
372 views

MyISAM vs InnoDB for Forums [closed]

I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
2
votes
2answers
8k 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
votes
1answer
1k views

Converting Heap to MyISAM issue

At work we have a problem that has now occurred more then once. Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill ...
2
votes
2answers
237 views

Whats row size in mysql?

i mean what does it mean ?, i have mysql db and table in myisam and it shows Space usage Type Usage Data 1.3 GiB Index 429.1 MiB Total 1.7 GiB Row Statistics Statements Value ...
2
votes
3answers
2k views

Why does MySQL slow down in loading data?

We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files. What's confusing is that the first file took only 2 minutes to load, whereas the ...
2
votes
1answer
6k views

Configuring MySQL (my.cnf) for MyISAM and InnoDB

I want to convert some (not all) the MyISAM tables to InnoDB. Here is my current my.cnf: pastebin.com/FW8YXtLQ What would be the best configuration? (need to add InnoDB settings, byecause there are ...
2
votes
1answer
207 views

Configuration required for a big mysql database around 20GB

I need to host a single machine database (constraints), wherein the database size is roughly 20gb. Most of the tables are in MyISAM. Any way I can actually run it with decent speed ? Alternatives I ...
2
votes
1answer
876 views

Issue moving database between servers (cannot find .frm file even though it's there)

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great. The name of my database is roster and it has one table called users. ...
2
votes
1answer
128 views

why/how does the number of matched columns influences the way of excecuting a query

Imagine the following situation: Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index. FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4 Table B uses InnoDB and ...
2
votes
1answer
406 views

Can MySQL 5.1 read a MySQL 5.5 MyISAM file?

We're in the middle of an upgrade from mysql 5.1 to mysql 5.5. Our only remaining holdout is the master, at 5.1, while all the slaves are at 5.5. However, we have a stats process that runs on an ...
2
votes
1answer
4k views

Repairing Myisam Table when there was no additional Disk space & Table corrupted

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner : mysql> select ...
2
votes
2answers
1k 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 ...
2
votes
2answers
572 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 ...
2
votes
1answer
446 views

MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs

We will install a webserver that writes access logs to a MySQL table. Access logs of course will get really really big, so this will be a separate MySQL instance from the regular data and we will more ...
2
votes
3answers
80 views

MySQL query takes over a minute to complete

I am trying to pull calls associated with website visits from our huge database... I have got SQL that works, pulls correct results it just takes over minute to complete... Here is the SQL SELECT ...
2
votes
4answers
213 views

If all tables are myisam, should I use myisam engine instead of default innodb?

I have read all of the innodb/myisam threads and they are confusing. Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.? The software I am using creates ...
2
votes
1answer
334 views

Will MyISAM prevent multiple users inserting data at the same time?

I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL ...
2
votes
2answers
528 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
2
votes
1answer
328 views

MySQL repair deleted data - Any chance of recovery?

Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was... mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at ...
2
votes
1answer
3k views

mysqlcheck --repair a specific table?

I logged in my server via SSH and committed a "repair table xxx" query on a MyISAM table with 34 million rows and 69GB of data. Unfortunately the SSH connection was lost. When I logged back in I found ...
2
votes
1answer
2k views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
2
votes
2answers
644 views

Why does MySQL fail to use an index effectively when LIMIT offset is large?

I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec: SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5; But querying ...
2
votes
1answer
279 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
2
votes
1answer
125 views

How safe is it to widen a column in MySQL?

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot. A MySQL database I volunteered to help maintain has a table that looks like ...
2
votes
1answer
3k views

how to set bulk_insert_buffer_size in mysql?

I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such ...
2
votes
3answers
1k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
2
votes
1answer
5k views

Merge multiple tables in a database

I have a MySQL database with table names like 1,2,3... upto 1000 Each tables have 2000 rows and has the same structure. This is how all my 2000 table's structure look like id,url,title,content id ...
2
votes
1answer
780 views

Is it safe to kill ANALYZE TABLE query on a MyISAM table?

I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an ...
2
votes
1answer
595 views

Repair with 1 thread

Why would MySQL be using Repair with 1 thread to rebuild the indexes on a table (1 primary key, 2 billion rows) when I have myisam_repair_threads set to 4?
2
votes
1answer
256 views

Is the key_buffer_size applicable to myisam tmp tables?

I have a database about 750GB in size. It's all innodb. Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The ...
2
votes
1answer
358 views

High IO when switched to InnoDB

We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times. The query is type of INSERT ... ON DUPLICATE KEY UPDATE I don't know if the problem is ...
2
votes
4answers
2k views

Very small MySQL table keeps crashing

I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per ...
2
votes
2answers
1k views

How can I check if the keys are enabled on a MyISAM table?

I have a script, which populates 4 tables. It goes like this: Disable the keys on all the tables Populate the tables Enabled the keys back. The problem is that windows decided to reboot the ...
2
votes
2answers
30 views

MySQL Database Archive

We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I we are planning archive inactive database.Is there any ...
2
votes
2answers
58 views

MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically returned dataset is always the ...
2
votes
1answer
110 views

Only InnoDB databases locking up

I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation. At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up. ...
2
votes
1answer
316 views

Need help in increasing maximum table size

Two of the tables vb_post, vb_profilevisitor getting corrupted again and again. The hosting company suggested the following: Looks like the problem is that the .MYD table was converted to a .TMD ...
2
votes
1answer
671 views

MySQL Error 1114 “table is full” Using MyISAM Engine

When executing the following query, we run into the error The table '/var/lib/mysql/temp/#sql_2d22_1' is full (actual table name varies). I've done a fair amount of research on this; most of the ...
2
votes
1answer
1k views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
2
votes
1answer
72 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 ...
2
votes
1answer
1k views

Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?

Per this post, in order to optimize the storage of a MyISAM table, you need to: OPTIMIZE TABLE ; ALTER TABLE ENGINE=MyISAM ; ANALYZE TABLE ; It's obvious what OPTIMIZE does for disk space but what ...
2
votes
1answer
471 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
2
votes
1answer
1k 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 ...
2
votes
1answer
466 views

MySQL table locks solution -> InnoDb / Partitions

I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows). It's mainly caused due many heavy inserts. My current thoughts to optimize: Changing to ...
2
votes
1answer
375 views

FLUSH TABLES stays in the processlist even after execution

Here is my code from Stored Procedures in Mysql FLUSH TABLES; TRUNCATE TABLE tbl_cdrload; TRUNCATE TABLE tbl_cdrdetails_temp; Then i execute a query to load a file in to database as below LOAD ...