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

1
vote
1answer
238 views

MySQL backing up MyISAM tables by copying the physical files - and automating the process

I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly ...
1
vote
1answer
45 views

MRG_MyISAM with myisampack tables blocking inserts

I'm using INSERT_METHOD=FIRST and compressing all tables that are members of the union but when I try to insert into the MRG_MyISAM table it tries to insert into the second one (instead of the first). ...
1
vote
1answer
55 views

MyISAM recover updated row

The row (with type MEDIUMTEXT) has been accidentally updated with empty value. I can see previous data in MYD file of a table, but I can't get them neither from mysqldump or any mysql client. If I ...
1
vote
1answer
308 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
1
vote
2answers
205 views

Why does MySQL + Windows Server = crashed tables periodically?

Windows Server 2008 R2 x64 MySQL Server 5.6.14 x64 Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by ...
1
vote
1answer
654 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
3answers
618 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 ...
1
vote
1answer
200 views

Debugging error report percona 5.6

I'm running percona 5.6 on a new centos 6.4 server. Things seem to work OK. I personally only really use InnoDB tables for my databases, but only the standard 'mysql' table is in MyISAM format. I ...
1
vote
1answer
326 views

Mysql Performance tuning for myisam tables.

Folks I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records ...
1
vote
1answer
68 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.
1
vote
1answer
592 views

Spatial Index on an InnoDB table

I have a database which is in InnoDB, because I wanted consistency (cascading delete). Now, I want to use spatial indexes for selecting rows ordered by geometric distance. However, spatial indexes ...
1
vote
1answer
2k 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 ...
1
vote
1answer
90 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
1
vote
1answer
260 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`; ...
1
vote
1answer
302 views

MySQL: OPTIMIZE after TRUNCATE?

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...
1
vote
1answer
85 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
1
vote
1answer
3k 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
1answer
314 views

Why do MySQL MyISAM table indexes go out of date?

I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes. Any idea, why this happens?
1
vote
1answer
843 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
1
vote
1answer
264 views

Set up replication and migrate to InnoDB with low downtime

Current situation is this: I have a large legacy DB using only MyISAM tables, the size of whole DB is 4GiB, one of the tables contains over 10 million rows, so it is pretty large. The database is ...
1
vote
2answers
2k views

MySQL not responding, Writing to net status

I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB. Here's the show ...
1
vote
1answer
104 views

How can I reinitialize some MyISAM tables without restarting MySQL?

I'm using rsync to copy some measurement data from one PC to the server. Is it possible to load the new MyISAM tables without restarting the MySQL server?
1
vote
1answer
814 views

MySQL INSERTs waiting for UPDATEs (MyISAM)?

In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables. We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
1
vote
1answer
183 views

What is the SQL command to get 20 closest points from some center location

There are 1.6 million rows. Must not compute distance for all rows. Spatial index is available This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added ...
1
vote
1answer
258 views

extremely slow MyISAM slave updates

I have 2 slaves and one is working as expected. The other slave is extermely slow while executing the "update" statements. The insert and deletes are fast. I used "check table tbl_name" to see if any ...
1
vote
1answer
2k 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 ...
1
vote
1answer
672 views

How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?

We have a keep-growing table which using MYISAM storage engine. Almost every half year, this table will crash, and need to be repaired. I checked the defragment result reported by windows ...
1
vote
1answer
143 views

MyISAM table row size is bigger than the sum of all the columns size?

I have a table: CREATE TABLE `playlist` ( `pid` int(11) unsigned NOT NULL, `mid` bigint(21) unsigned NOT NULL, `status` tinyint(1) NOT NULL, `add_time` int(11) unsigned NOT NULL, ...
1
vote
0answers
25 views

“Unexpected EOF” during SELECT on huge MyISAM table

When executing a SELECT query that uses filesort on a huge table (2 billion rows) the execution stops after ~30mins with the following error message: ERROR 9 (HY000) at line 1: Unexpected EOF found ...
1
vote
1answer
23 views

How do i increase the total MyISAM indexes?

I have just made a check for my server with mysqltuner. -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster ...
1
vote
1answer
48 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
0answers
43 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
1
vote
0answers
61 views

Question on MySQL insert race condition

I'm using MySQL 5.1, PHP 5.3 and the table is MyISAM. Here's the DTD of this very fake table: CREATE TABLE `temp1` ( `value` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 This ...
1
vote
1answer
83 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
1
vote
1answer
102 views

Mysql sequential numbering based on MAX of another column

I've a table lp_pictures which have these fields: [id | plate_uid] [1 | aaa] [1 | aaa] [1 | aaa] [1 | bbb] [1 | bbb] [1 | bbb] and i need to UPDATE the field id to becomes: [id | plate_uid] ...
1
vote
0answers
193 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
1
vote
0answers
1k views

MySQL - high CPU usage (MyISAM tables)

We have a server running LAMP stack on CentOS release 5.6 (Final) MySQL is having high CPU usage constantly which causes server to lag as shown top - 13:50:47 up 80 days, 13:53, 1 user, load ...
1
vote
1answer
719 views

mysqldump: Does --lock-tables=false work with MyISAM tables?

So, I have to check and come with a backup solution for a very busy mysql server (with tables over 5 GB). Due to resources constraints, they choose to set all tables with the MyISAM engine. Currently, ...
1
vote
1answer
152 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 ...
1
vote
1answer
102 views

InnoDB to MyISAM Conversion Problem

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value ...
1
vote
0answers
177 views

Missing rows in MyISAM tables with 10-20 billion records

I've got 2 tables with 10-20 billion records each. Currently these are in MyISAM (MySQL). They only require to be built and indexed once, after this only SELECT is used. They are currently ...
1
vote
2answers
562 views

Setting default CHARSET and DB Engine

I recently updated my server's database system from MySQL to MariaDB. After update, everything works well. But, when I went to phpMyAdmin, the default charset and types are changed: At the end of ...
1
vote
1answer
328 views

MyISAM key buffer

Good evening, I want to know how can I use informations about MyISAM to calculate : size of buffer, size of buffer used, Write ratio, Read ratio, and MyISAM key cache hit rate and thank you.
1
vote
1answer
534 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 ...
1
vote
2answers
425 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
212 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
1
vote
0answers
131 views

How can I force myisamchk to modify the original data file in case of duplicate keys?

In the documentation for myisamchk, it says: --quick, -q Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to ...
1
vote
0answers
179 views

How to speed up this query

MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
1
vote
0answers
107 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE ...
1
vote
1answer
2k views

Write lock during mysqldump even using --lock-tables=false

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this? As I am using MyISAM, does --single-transaction for InnoDB help? ...