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

4
votes
1answer
602 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 ...
4
votes
2answers
672 views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
4
votes
3answers
4k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...
4
votes
1answer
5k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
4
votes
1answer
23k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
4
votes
2answers
3k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE ...
4
votes
2answers
144 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 ...
4
votes
2answers
1k views

How to speed up mysql export and import

I need to perform a DB migration from Server A to Server B. Server A: MySQL version installed 5.0.6 Server B: MySQL version installed 5.6.20 OS: Both are running RedHat Linux 64bit. 16 ...
4
votes
1answer
397 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 ...
4
votes
1answer
4k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
4
votes
1answer
4k views

Setting up replication for MyISAM tables?

Before setting up a replication within MySQL, the data first needs to be transferred to the slave. Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, ...
4
votes
2answers
825 views

Less RAM than Index_length MyISAM

I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
4
votes
2answers
1k views

How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast. The problem is, from research, MyISAM is okay with being ...
4
votes
2answers
260 views

Choosing between MyISAM & InnoDB - two servers with related tables

I don't want to make a mistake. I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB. I read here and there that InnoDB should be ...
4
votes
1answer
231 views

Can I partially invalidate the MySQL table cache?

I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB. After several hours, MySQL invalidates the entire table from cache, while < ...
4
votes
1answer
3k views

Not enough space for MySQL optimize; do new rows overwrite existing data?

My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space. Is there any ...
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, ...
4
votes
2answers
208 views

Optimal engine for small lookup tables in MySQL

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...
3
votes
2answers
1k views

Why innodb doesn't store geospatial data?

I am very curious. I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam. But why would I do so? Why can't innodb store ...
3
votes
3answers
620 views

Dynamically changeable database

We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
3
votes
2answers
1k views

How to backup a MySQL database without slowing the application?

I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
3
votes
1answer
4k views

mysql table very large size although it doesn't have a lot of records

I have drupal website, one of the tables is cache_form. I have a cron to DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW()); which works fine. The size of cache_form.myd is 4.9G ...
3
votes
1answer
2k views

mysqlcheck to optimize innodb tables — performance/scheduling

I'm attempting to optimize tables on a very large, production database server. I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables. Is there a ...
3
votes
2answers
7k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
3
votes
1answer
183 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
3
votes
2answers
381 views

Writing transactions to .MYD file to take Backup

I'm trying to backup MySQL DB by archiving the data directory of required databases. I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
3
votes
2answers
92 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 ...
3
votes
2answers
82 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 ...
3
votes
1answer
1k views

mysql: find column data size on disk

I have fairly large MyISAM table with longtext field in it Table size is somewhat 16GB. I need to know if clearing this column in old records helps to keep table small I can retrieve table wise ...
3
votes
1answer
447 views

MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs

We will install a webserver that writes access logs to a MySQL table. Access logs of course will get really really big, so this will be a separate MySQL instance from the regular data and we will more ...
3
votes
1answer
2k views

Tuning key_reads in MySQL

We are trying to tune our MySQL database (Myisam, 5.1.73) which is getting busy. We investigated Key_reads: $ mysqladmin ext -ri10 | grep Key_reads During a normal load we have the following ...
3
votes
1answer
6k 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 ...
3
votes
1answer
423 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
3
votes
1answer
5k views

MySQL high CPU usage (MyISAM table indexes)

I have a problem with an inherited MySQL database. From time to time mysqld uses up to 2300% CPU.. The only solution is to service mysql stop and run an myisamchk -r on a table. After the indexes have ...
3
votes
3answers
1k views

MySQL Tables Require Daily Repairs - Server, Table or HD?

I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total. The bigger ones ...
3
votes
1answer
2k 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 ...
3
votes
1answer
2k views

MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing: We're writing some code to migrate our messages to a new table called communications. We're ...
3
votes
2answers
6k views

Table compression in InnoDB?

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? ...
3
votes
2answers
1k views

Quick MySQL Backup

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack. Or mysqldump will work ...
3
votes
1answer
481 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: > +-----------------+-------+----------+----------------------------------- | > ...
3
votes
1answer
838 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 ...
3
votes
2answers
2k views

Repair MySQL table without downtime

We have a corrupt MyISAM table in our production MySQL database. Is there a method for repairing this table without locking it? We were considering using a method similar to Facebook's Online Schema ...
3
votes
1answer
932 views

Fastest way to copy data from MyISAM to InnoDB

I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes. However, every approach I try ...
3
votes
1answer
2k views

MySQL Config for Large MyISAM tables

I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get ...
3
votes
1answer
4k 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 ...
3
votes
1answer
952 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 ...
3
votes
1answer
907 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
3
votes
2answers
342 views

Are there implicit indexes in InnoDB like MyISAM?

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys: (a) (a, b) (a, b, c, id) Is this valid for ...
3
votes
1answer
452 views

Can I use mysql to serve 100's of millions of small image files?

I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
3
votes
1answer
438 views

Which MySQL table to use for read-only, bulk-loaded tables?

So I have a workload on 2 key tables in my application with the following characteristics: READ-ONLY DATA The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...