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

3
votes
3answers
442 views

How can I select using the same fields from an undetermined number of tables using MySQL?

I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this: foo_reference1 foo_reference2 foo_referencea foo_referenceb .... ...
3
votes
2answers
685 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
515 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
800 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 ...
3
votes
2answers
791 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
2answers
662 views

Should I move to InnoDB during a planned migration?

I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
3
votes
1answer
302 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 ...
3
votes
2answers
4k 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
134 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
4answers
6k views

mysql insert into indexed table taking long time after few million records

I have a table like this CREATE TABLE IF NOT EXISTS `dnddata` ( `numbers` varchar(10) NOT NULL, `opstype` char(1) NOT NULL, PRIMARY KEY (`numbers`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ...
3
votes
2answers
331 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
1answer
3k views

Why full-text-search returns less rows than LIKE

I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists. Example statements: SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE ...
3
votes
1answer
222 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
3k 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
590 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
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 ...
3
votes
1answer
1k 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
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
335 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
3k 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 ...
3
votes
1answer
641 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
502 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
199 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
256 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
201 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, ...
3
votes
2answers
163 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 ...
3
votes
1answer
911 views

mysql, changing tables from myisam to ndb

Gents, Converting a table from myisam to ndb via: ALTER TABLE piwik_log_profiling ENGINE NDBCLUSTER; This will fail with: BLOB column 'query' can't be used in key specification with the used ...
2
votes
3answers
496 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
2
votes
1answer
3k 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 ...
2
votes
2answers
90 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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
320 views

Issue moving database between servers (cannot find .frm file even though it's there)

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great. The name of my database is roster and it has one table called users. ...
2
votes
1answer
119 views

why/how does the number of matched columns influences the way of excecuting a query

Imagine the following situation: Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index. FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4 Table B uses InnoDB and ...
2
votes
1answer
337 views

Can MySQL 5.1 read a MySQL 5.5 MyISAM file?

We're in the middle of an upgrade from mysql 5.1 to mysql 5.5. Our only remaining holdout is the master, at 5.1, while all the slaves are at 5.5. However, we have a stats process that runs on an ...
2
votes
1answer
726 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 ...
2
votes
2answers
800 views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
2
votes
2answers
509 views

Any problem will all InnoDB and one MyISAM table?

I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
2
votes
1answer
38 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 ...
2
votes
2answers
281 views

Why does MySQL fail to use an index effectively when LIMIT offset is large?

I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec: SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5; But querying ...
2
votes
1answer
355 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
2
votes
1answer
76 views

How safe is it to widen a column in MySQL?

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot. A MySQL database I volunteered to help maintain has a table that looks like ...
2
votes
1answer
443 views

Is it safe to kill ANALYZE TABLE query on a MyISAM table?

I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an ...
2
votes
1answer
325 views

Repair with 1 thread

Why would MySQL be using Repair with 1 thread to rebuild the indexes on a table (1 primary key, 2 billion rows) when I have myisam_repair_threads set to 4?
2
votes
1answer
189 views

Is the key_buffer_size applicable to myisam tmp tables?

I have a database about 750GB in size. It's all innodb. Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The ...
2
votes
1answer
994 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 ...
2
votes
2answers
774 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 ...
2
votes
1answer
2k 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, ...
2
votes
4answers
1k views

Very small MySQL table keeps crashing

I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per ...
2
votes
1answer
3k views

Repairing Myisam Table when there was no additional Disk space & Table corrupted

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner : mysql> select ...
2
votes
2answers
3k 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? ...