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

2
votes
1answer
130 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
1
vote
1answer
77 views

Can I emulate Oracle's autonomous transaction in MySQL?

I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any ...
1
vote
1answer
122 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
1
vote
1answer
148 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
1answer
188 views

Concurrency Issues when multiple users uses Portal (MySQL and PHP)

We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no ...
0
votes
1answer
59 views

MySQL Performance Tuning for MyISAM

I need to performance tune a mysql server and need help. I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are ...
0
votes
1answer
54 views

Repair by Sort not working

I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql ...
0
votes
1answer
81 views

Mysql is shutting down in every 5 minutes

My MySQL 5.6 is shutting down about in every 3-5 minutes. I only can use it after restart. I don't know what happened; everything was OK 2 days ago. What do you suggest to me? Here is my ERR file ...
0
votes
1answer
47 views

Global Read Lock for Snapshot of MySQL Data

I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: ...
0
votes
1answer
75 views

Will 'sleeping' connections open files other than the socket in MySQL?

Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number ...
0
votes
1answer
89 views

Change Storage Engine on Referenced Tables

i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names ...
0
votes
1answer
1k views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
0
votes
1answer
268 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
3
votes
0answers
536 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
0answers
29 views

Tuning Issue / Temporary Tables

I've got a MySQL database (MyISAM) that handles a lot of read/writes and uses temporary tables quite frequently. Recently it's started having massive load spikes at peak load, and noticed that there ...
2
votes
0answers
2k 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 ...
2
votes
0answers
126 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
1
vote
0answers
52 views

Cardinality of an index in a MySQL table is equal to table's row size

I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct ...
1
vote
0answers
42 views

Mysql slow copying to tmp table to indexing

I try to indexing table. The table size is about 60GB. But indexing query is hang on 'copying to tmp table' 18 hours. I found advice setting 'myisam_sort_buffer_size' myisam_sort_buffer_size : 128M ...
1
vote
0answers
58 views

Tuning MySQL MyISAM for large joined updates

I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
1
vote
0answers
48 views

Migrated servers… Innodb tables stayed innodb, but database is showns as MyISAM. Problem?

I moved servers. I exported DB on old, imported to new. Kept getting syntax error on import until I tried no compression. Weird, but it worked. I was skeptical so I compared the two carefully and ...
1
vote
0answers
259 views

Suddenly high CPU usage

I've read a lot about MySQL optimisation and I already optimised MySQL about a year ago. Reason for that was a high system load average. I tuned many queries and edited the my.cnf file. Since then ...
1
vote
0answers
217 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
0answers
110 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
146 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
0answers
847 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
366 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
0answers
189 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
192 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 ...
0
votes
0answers
48 views

How I can reduce disk IO in MySQL 5.6.26?

I was using MySQL version 5.1.52 for my application but recently I have updated it to 5.6.26, but now I am facing very high disk IO. Following are system configuration: OS : Linux 2.6.39.4 RAM : ...
0
votes
0answers
28 views

myisamchk: Why is there a huge time difference running myisamchk in real and user time?

We have some MySQL MyISAM databases, whose backups are taken using mysqlhotcopy Thought to check the backups integrity, and errors (if any) and hence run myisamchk. I was tracking the possible ...
0
votes
0answers
54 views

Why my MySQL (myisam) index is broken and how to avoid it?

I have big composite (multiple-column) index on MySQL MyISAM table. And I noticed it doesn't work. I ran SHOW INDEX command. There are NULL in the Cardinality column on the most index fields (except ...
0
votes
0answers
39 views

Updating 2M rows based on 2M individual rules (MYSQL)

I'm carrying out a fairly simple (but large) Database combine/migration (simple/dumb, but slow work). It's 2 systems that are very similar, but do have slight differences. Currently the main issue is ...
0
votes
0answers
38 views

MYISAM can't create table: Row size too large

We are having database in MySQL 5.1, we have taken the DB backup from MySQL 5.1 and trying to restore in MySQL 5.6. We are getting below error: ERROR 1118 (42000) at line 603: Row size too large. ...
0
votes
0answers
58 views

How can I restore a MySQL dump faster on Windows?

I am restoring some InnoDB and MyISAM .sql files that are on the order of GBs (ranging from 1 GB to ~ 10GBs) and they are taking a day or longer to restore. The command I am using currently is: use ...
0
votes
0answers
10 views

MySQL editable reports from multiple identical tables

Let's say I have two sets of four identical tables for my sales that are generated by our apparatus. The first set represents each sales made in a region. Sales_Canada_Est, Sales_Canada_West, ...
0
votes
0answers
80 views

Got an error reading communication packets

Facing error in MySQL Log which is "Got an error reading communication packets" for multiple IP's. Current MySQL version is 5.0.67 running in Linux Things looked into so far are as follows ...
0
votes
0answers
115 views

MySQL - high CPU usage (MyISAm tables) in LAMP

I have a LMAP stack running on Centos 6.6. Here is the detail which may help. # cat /etc/redhat-release CentOS release 6.6 (Final) MySql version # rpm -qa | grep -i mysql ...
0
votes
0answers
71 views

Got an error from unknown thread, /pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/mysql-5.5.40/mysql-5.5.40/storage/myisam/mi_write.c:226

I am using mysql 5.5.40-log and my default storage engine is Innodb, I found this error in my error log "[ERROR] Got an error from unknown thread, ...
0
votes
0answers
166 views

How to backup huge Mysql db containing both InnoDB & MyISAM tables in Aws RDS?

I am running a Mysql RDS with 150GB+ data. The DB size is growing very fast(But normal considering the application's nature) Till now we were having only InnoDB storage engine, so we were using the ...
0
votes
0answers
24 views

Default engine innodb problem

My MYSQL Version is 5.6.12 so the default engine should be InnoDB and is the same. I created few functions and procedures using MYSQL Query Browser but it went and got saved in a database called ...
0
votes
0answers
48 views

Slow queries while using views in MySQL

We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or select * from theView where user_id = 1234 kind of queries when searching. When I ...
0
votes
0answers
334 views

I have changed table engines from MyISAM to Aria, should I change the database engine too?

I have changed all tables in my mariadb database from MyISAM to Aria (using adminer as tool), but the database engine is still shown as InnoDB. Should I change this to Aria, too? I did not choose ...
0
votes
0answers
829 views

Benchmarking MyISAM vs InnoDB using Sysbench on MariaDB-10.0.14

I performed a "read" benchmark on MariaDB-10.0.14 db server using sysbench to compare performance between InnoDB and MyISAM on a single. Below are the sysbench commands i used: Create test table in ...
0
votes
0answers
221 views

MySQL: after query optimization the CPU is still over 100%

my first time here! First of all I want to say that I'm not a DBA and I have a very little experience with MySQL but I like to study and understand what I'm doing. I have a virtual machine (2gb RAM, ...
0
votes
0answers
847 views

How to recover a crashed MyISAM table which myisamchk couldn't recover?

We take db backup by stopping the database and then issuing a tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error ...
0
votes
0answers
146 views

What are the steps to change tables engines from MyISAM to InnoDB?

I have a Datebase with 10MB size . The Tables' engines were MyISAM (table-lock level). So I deiced to change them to InnoDB (row-lock level). I used the sql query for each table : ALTER TABLE t ...
0
votes
0answers
353 views

Improve Speed of MySQL Database Import

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 ...
0
votes
0answers
23 views

Myisam Mysql server crashed

I have a server with linux fedora 11 Operating System. Recently , I discovered that every morning mysql has been crashed, so I have to restart mysql service. Can any body help me?