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
7 views

MySQL 5.5 vs 5.6 with WordPress

We have a moderately busy WordPress blog that runs currently on MySQL 5.5, and I'm wondering if I would experience any significant performance upgrades by moving it to MySQL 5.6? Anyone know? I'd ...
-1
votes
1answer
13 views

How can we migrate Data with MySQL 5.1 - MyISAM to another server with MySQL 5.6 - InnoDB

Currently we have a MySQL 5.1 - MyISAM database on a physical server. We need to move it to another server with MySQL 5.6 - InnoDB at another location. DB size is quite large - 210GB Can someone ...
-1
votes
0answers
19 views

ways to decrease physical memory usage and commit charge (Task Manager) mysql execute query

my tables are Myisam i want to know ways to decrease memory usage and commit charge while execute query because my server ran out of memory any cache service can i stop to reduce physical memory ...
0
votes
0answers
14 views

What triggers/prevents MyISAM Flushing

I have a database that has between 10k and 400 CUD operations/s on MyISAM tables. Whilst the number is slightly lower at night, I see a recurring pattern night after night which causes a backlog to ...
1
vote
1answer
34 views

Sync two MySQL servers data, while changing the Engine in the slave

I'm running a MySQL server 5.5.44, and I'd need to sync its data to a new server running MySQL 5.7.13 using master-slave. The idea is to bring both DBs synced until we migrate to the second, newer ...
2
votes
2answers
100 views

Will hacking away ibdata1 destroy databases with only MyISAM tables?

I made a mistake. I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so. I know that safely shrinking ...
1
vote
2answers
67 views

Converting from InnoDB tables to innodb_file_per_table

I run MySQL and I have 7 schemas, 6 of the 7 schemas are MyISAM. In one of my schemas I have 20 tables that are InnoDB, the rest are MyISAM. I really want to convert those 20 tables over to ...
0
votes
1answer
41 views

MySQL MyIsam Compressing tables

Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows) Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
1
vote
2answers
34 views

MySQL CREATE INDEX slowing down

My analytics table has 190M rows, about 150GB ins size. Storage engine is MyISAM with key-buffer-size=16G myisam_sort_buffer_size=2G myisam-max-sort-file-size=200G. Machine has 32GB memory. While ...
0
votes
0answers
19 views

Copying MySQL MariaDB databases MyISAM vs Aria

One of the main advantages that MyISAM continues to have over InnoDB is the ability to copy databases directly between servers using just tar files of the database directories. InnoDB has ...
1
vote
1answer
39 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
3
votes
1answer
33 views

Mysql table crash, what do I need to do to fix this

Table '.\mysql\proc' is marked as crashed and should be repaired. Can anyone help me figure out how to fix this?
1
vote
0answers
27 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
1answer
194 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
64 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
31 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
1answer
39 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
57 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
50 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 236....
0
votes
0answers
56 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
38 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 pt-online-...
1
vote
1answer
174 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 ...
0
votes
0answers
42 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 ...
1
vote
0answers
65 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
2answers
171 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 != '...
0
votes
0answers
85 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
1answer
87 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 ...
0
votes
0answers
98 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 ...
2
votes
3answers
90 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 ...
1
vote
0answers
71 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
51 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
81 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
167 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
75 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, p2s....
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 ...
0
votes
0answers
11 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
109 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 ...
1
vote
2answers
109 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 ...
0
votes
2answers
92 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
32 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 ...
1
vote
1answer
251 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 ...
0
votes
1answer
61 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: ...
3
votes
2answers
116 views

Is it possible to block a specific query, or return a static result?

Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on ...
0
votes
1answer
199 views

Fulltext Search Doesn't Match a Hashtag

We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns: CREATE TABLE `forums_posts_search` ( `post_id` bigint(20)...
0
votes
2answers
50 views

What storage engine do I need - Mysql?

I have designed a database and came to the point of actually deciding which storage engine is better for my case. I estimate that during the first few years I would have around 100,000 - 500,000 ...
1
vote
3answers
323 views

Convert MyISAM to InnoDB

What are the considerations needed when converting myisam to innodb. I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to ...
1
vote
0answers
59 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 ...
3
votes
2answers
124 views

Database Tuning Issues

I'm not a DBA so looking for some advice on tuning a MySQL (MyISAM) database. I've found that during peak hours we are getting pretty severe load spikes which are slowing down the web front-end. I've ...