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
1answer
2k 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 ...
1
vote
0answers
22 views

Can MyISAM tables affect the backup recovery of innodb tables on Amazon RDS?

I have an Amazon RDS instance containing several databases. All the tables are innnodb except from one, that is MyISAM and currently cannot be converted to innodb. I know that the recovery of MyISAM ...
0
votes
2answers
2k 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 ...
2
votes
1answer
137 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 ...
3
votes
5answers
14k 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 ...
0
votes
1answer
280 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 ...
1
vote
1answer
128 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' ...
0
votes
2answers
195 views

Best configuration for MySQL? InnoDB and MyISAM

I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)... but results haven't turned ...
2
votes
1answer
1k views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
0
votes
1answer
100 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
0answers
54 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
29 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 ...
1
vote
1answer
151 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 ...
0
votes
1answer
30 views

Automatic Recovery of MyISAM table

I have an application in C language which inserts the report data in MyISAM table by using mysql_stmt_execute() function. Due to load on the table, it gets crashed. For auto repair of crashed table, ...
0
votes
1answer
55 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 ...
5
votes
1answer
837 views

Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
0
votes
1answer
48 views

Can't import .sql file

I have a 1.4GB .gz backup file(which is 8GB unzipped) I have to import to the server but it isnt working whatever method I have tried. 1st I tried Workbench, after 2 days and still no progress I ...
3
votes
2answers
62 views

Slow MySQL query

I have 3 tables: Name Engine Rows Data Size Index Size Total Size product MyISAM 11.06M 859.59M 861.20M 1.68G product_manufacturer MyISAM 3.09K ...
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 ...
1
vote
1answer
101 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
32 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the ...
0
votes
0answers
40 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 ...
3
votes
1answer
858 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
0answers
57 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 ...
0
votes
0answers
40 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
2answers
102 views

MariaDB/Galera Cluster with 3 nodes - Change ENGINE from MyISAM to InnoDB without crash

We have tables with MyISAM. The are out of sync, the number of records is different on each cluster node > select table_schema, table_name from tables where ENGINE = 'MyISAM' and table_schema != ...
2
votes
1answer
90 views

Moving the binary location of MySQL for replication

Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump > for export and mysql < for insert on the slave? I want this because in our current ...
0
votes
1answer
67 views

Index on integer and string fields

A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII ...
2
votes
3answers
88 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 ...
0
votes
0answers
63 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 ...
4
votes
1answer
4k views

MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-'

We have a master & multiple slave replication in our production environment. At first, I found this error in the mysql error log on the master and later on the slave. As for all the replication, ...
1
vote
0answers
57 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
2answers
36 views

Fulltext searching on shared hosting - 2000 Entires with around 500 words each

I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be ...
1
vote
1answer
77 views

Innodb composite primary key with 4 columns vs MyIsam

I've created "MY TABLE" based on Innodb with composite primary key because I am storing many trees inside this table. The PK is for example (a,b,c,d). 1- Does Innodb store rows with same (a,b,c) near ...
4
votes
2answers
151 views

Integrate MySql Full Text searches in CMS

I'm developing my own CMS and using MySql v5.5 DB to save the content items records. Due to a technical limitations, I can't upgrade my database to a newer version that support Full-Text searches on ...
1
vote
1answer
72 views

Small database size but very slow performance. What can I do?

We have this opencart installation with a database size of roughly 120 MB, 90MB of which is MyISAM and other is InnoDB. Most of the queries like : SELECT DISTINCT *, a.*, vd.seo, p.image, ...
6
votes
4answers
17k 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 ...
2
votes
2answers
32 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 ...
16
votes
3answers
10k 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) ...
1
vote
2answers
92 views

Optimize speed on 400GB MyISAM table

We have MariaDB server with single table with image url information. The table is about 400GB on disk and contains probably 400M rows. Table is partitioned in 1024 partitions. All queries are ...
5
votes
2answers
637 views

MySQL fulltext selection performance

I have 1.5M rows in a table. Following is the table create code: CREATE TABLE `jobs` ( `id` INT(8) NOT NULL AUTO_INCREMENT, `job_id` VARCHAR(50) NOT NULL DEFAULT '', `title` VARCHAR(255) ...
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
1answer
93 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
2answers
77 views

MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa?

I have an mu WordPress database and I want to change it from MyISAM to InnoDB to test its performance. Which MySQL command/commands will do it for all of the tables instead of doing manually one by ...
1
vote
1answer
186 views

How do I insert tens of thousands of rows without locking?

I have a server that receives data from thousands of locations all over the world. Periodically, that server connects to my DB server and inserts records, one row at a time in rapid succession. There ...
1
vote
1answer
31 views

why MyISAM uses 3 files per table? [closed]

Why MyISAM uses 3 files per table? I know one of the file contains table definition, another contains primary key and all other indexes, third the data. What is unclear for me, what is the benefit ...
2
votes
1answer
430 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 ...
4
votes
2answers
2k views

Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for ...
0
votes
1answer
52 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: ...
7
votes
2answers
17k 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 ...