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

0
votes
0answers
4 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 ...
0
votes
1answer
11 views

What does FTS_ file mean after converting from MyISAM to InnoDB?

I have convert some tables in database from MyISAM to InnoDB. After that, I see some modification on file system. The size of database is grown up, from 2.8GB to 4.8GB and there are many file with ...
0
votes
1answer
19 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 ...
2
votes
1answer
27 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 ...
0
votes
1answer
20 views

moving binary database folder is causing issues with innodb tables

so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables ...
0
votes
1answer
24 views

Query perfomance with multiple JOINs on MyISAM-based DB

I'm working with Enron Email Dataset in MySQL database. After I executed data import from dump-file into MyISAM-based DB, I got the set of tables without any connections between them via foreign keys. ...
0
votes
0answers
74 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 ...
1
vote
1answer
38 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 ...
0
votes
2answers
62 views

MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem ...
1
vote
0answers
41 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
250 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
38 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 ...
0
votes
1answer
28 views

Mysql - MyISAM Default order

We are using Mysql V5.6. We have a MyISAM table. when we open the table, the records will be displayed in some order instead of primary key order. Even when selecting the records will not be retrieved ...
1
vote
1answer
52 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
49 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
1answer
30 views

Tables with no rows not empty

I've got a couple of tables that get cleared out regularly by a cron job on a web server. However when these tables are completely empty it looks like there is still data in them. e.g. This database ...
3
votes
1answer
292 views

Is there any performance difference in using a unique index on primary key?

I know there are lost of similar questions to this one, but none that really answers this: Is there ANY difference in performance in using an unique index in a primary key, using a MyISAM table on a ...
10
votes
2answers
191 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, ...
0
votes
1answer
27 views

I'm building up a large MyISAM table from a few dumps. The first 21GB took less than 10 hrs total. The next 5GB is forecasted to take two months

I had a couple machines building up identical MyISAM tables. I've dumped all of those tables to a few files, and am now trying to restore them all into a single table on my own machine. The first ...
8
votes
2answers
330 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) ...
4
votes
1answer
297 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
2
votes
1answer
767 views

What could cause waiting for table metadata lock?

I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB ...
0
votes
1answer
146 views

Why is my Mysql Query verry slow when selecting a text field

i have a problem with a left join. Tables are myisam. Tables CREATE TABLE `article` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `force_startpage` tinyint(1) DEFAULT '0', `title` varchar(255) ...
0
votes
2answers
72 views

Alter auto-increment of table with 16 million rows

Learned this the hard way. Need to alter a table's auto-increment with 16 million rows. Now, it's taking quite sometime. Any estimate how long it would take? Sorry.
0
votes
4answers
272 views

Converting to MyISAM, `foreign_key_checks = 0` Not Working

At the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables. However, when I attempt to alter ...
0
votes
0answers
400 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 ...
1
vote
0answers
71 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
51 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). ...
2
votes
2answers
80 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 ...
1
vote
1answer
129 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 ...
0
votes
1answer
96 views

How to transfer 70GB MySQL with no downtime?

I have a large amount (4000) of individual databases, 90% of which are small. They all include about 20 tables each, and in total, they use about 75GB of space. All tables are MyISAM. I want to ...
0
votes
0answers
12 views

What is data like *after* fixing a corrupt MyISAM or InnoDB table?

I collect user data and store in an a database. Some of the tables are MyISAM and some are InnoDB. Lately, both have been experiencing regular corruption for reasons not clear. I know that restoring ...
0
votes
0answers
27 views

How do I stop views (large data sets) from freezing my database

I am working with tables that contain upwards of 200,000 rows. My boss wants to use views that refer to other views in order to have things be more automated. The problem is that the MySQL server ...
0
votes
1answer
44 views

MyISAM InnoDB performance

MyISAM vs InnoDB I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself. When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver. ...
0
votes
0answers
86 views

What is a good database design (schema) for an article search engine application?

I'm trying to make a article search engine application. There are about 1 million articles that will be stored in the DB. I've tried looking around for some similar examples, but I couldn't find any ...
0
votes
2answers
63 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
264 views

Queries are taking long time to execute

I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine. Problem started 3 days back, there is long list ...
2
votes
1answer
126 views

mysql Wrong bytesec: 0-0-0 at linkstart: 0

I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns: > +-----------------+-------+----------+----------------------------------- | > ...
0
votes
1answer
461 views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
0
votes
0answers
111 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, ...
1
vote
1answer
77 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
120 views

MyISAM : Should I be concerned by these warnings?

I restored some tables from recent Databases backups. Because Im supporting a legacy system that only uses MyISAM tables in their DBs, for backing up I used a little script that pretty much copies all ...
1
vote
1answer
118 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
227 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), ...
2
votes
1answer
201 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
375 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 ...
8
votes
2answers
275 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 ...
2
votes
1answer
162 views

Does MySQL guarantee atomic upgrade from read lock to write lock?

If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the ...
1
vote
1answer
69 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
2
votes
1answer
578 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 ...