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

2
votes
0answers
85 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
7
votes
1answer
2k views

MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`

I know very little about database administration but I have to deal with some very large tables on my site. This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
0
votes
1answer
115 views

Two identical MySQL tables, one not using indexes

I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data. Schema of one table CREATE TABLE `measuresHistory` ( ...
2
votes
1answer
326 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?
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 ...
1
vote
1answer
323 views

Why do MySQL MyISAM table indexes go out of date?

I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes. Any idea, why this happens?
0
votes
2answers
2k views

MySQL Table not repairing

Table info: Database name: user_motiva Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD when I do a mysqlcheck -r --all-databases it gets hung on that table even if you ...
1
vote
1answer
293 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
4
votes
2answers
1k views

Index before or after bulk load using load infile?

I have a database with over 1B rows and two columns that are indexed (in addition to the PK). Is it better to have the index pre-defined in the table before the load infile or better to index after ...
6
votes
1answer
165 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
1
vote
1answer
887 views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
1
vote
1answer
253 views

High IO when switched to InnoDB

We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times. The query is type of INSERT ... ON DUPLICATE KEY UPDATE I don't know if the problem is ...
0
votes
1answer
182 views

Large queries on MyISAM tables crashing MySQL

I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
0
votes
0answers
22 views

Myisam Mysql server crashed

I have a server with linux fedora 11 Operating System. Recently , I discovered that every morning mysql has been crashed, so I have to restart mysql service. Can any body help me?
0
votes
1answer
363 views

MySQL: replicating to a different table engine type

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
1
vote
1answer
342 views

MySQL: OPTIMIZE after TRUNCATE?

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...
1
vote
1answer
85 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
1
vote
1answer
4k views

MySQL my.cnf Optimization

We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance. We have tried to configure my.cnf but heavy queries got ...
0
votes
1answer
43 views

Reinsert data in new table with new IDs

I have the following two tables: buynsell CREATE TABLE `buynsell` ( `id` VARCHAR(20) NULL DEFAULT NULL, `msg` VARCHAR(255) NULL DEFAULT NULL, `date` VARCHAR(25) NULL DEFAULT NULL, ...
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 ...
6
votes
2answers
2k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
1
vote
1answer
188 views

LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE. But we have 1GB each day we want to update in a batch process using ...
5
votes
3answers
7k views

MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done. I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
0
votes
2answers
590 views

Bypass MyISAM table lock

I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
1
vote
1answer
1k views

View dropped, now shows as table “in use”, can't drop

I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid. I tried dropping the view so I could recreate it, then MySQL said it was a table ...
2
votes
1answer
294 views

FLUSH TABLES stays in the processlist even after execution

Here is my code from Stored Procedures in Mysql FLUSH TABLES; TRUNCATE TABLE tbl_cdrload; TRUNCATE TABLE tbl_cdrdetails_temp; Then i execute a query to load a file in to database as below LOAD ...
0
votes
2answers
1k views

How do I get spatial index feature if I mainly use innodb?

My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join. Is this a good idea?
2
votes
1answer
946 views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
2
votes
2answers
669 views

Need help in restore some rows into mySQL database frm myd myi

I am using one mySQL database for several sections of the website and one of the section is not working, when I go to the link of that section it gives the error "1146 Error @ Line 39: Table ...
1
vote
1answer
333 views

Does MySQL's MyISAM use cache before insert?

I want to test two different Disks, one of them is HDD and another is SSD I use exactly same information and app for both servers. They are exactly same, just one of them is SSD and another is HARD. ...
1
vote
1answer
897 views

MySQL INSERTs waiting for UPDATEs (MyISAM)?

In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables. We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
0
votes
1answer
241 views

Why number of matches in fulltext search get less when we put less strings?

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('resta*' IN NATURAL LANGUAGE MODE) show 5 results SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('restaurant*' ...
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 ...
1
vote
1answer
112 views

Issue with Storage Engine

Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6. Somehow, my ALTER TABLE was successful. Can you please let me know ...
1
vote
2answers
482 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
0
votes
0answers
33 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
1
vote
1answer
142 views

With MyISAM is there any index size savings when using INT vs BIGINT?

There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type. But I have been trying to figure out if there is also any space savings for myisam indexes on ...
5
votes
2answers
263 views

Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Followings are what I need to do on my project: 1- If a search term is new and unique, then add the search term 2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
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, ...
4
votes
1answer
2k views

Proper tuning for 30GB InnoDB table on server with 48GB RAM

I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
4
votes
2answers
352 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 ...
1
vote
1answer
600 views

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it). We are loading changes to this table in a nightly batch job using LOAD ...
0
votes
1answer
432 views

MYSQL MyISAM data recovery implementation

I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or ...
9
votes
2answers
12k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
1
vote
1answer
271 views

Set up replication and migrate to InnoDB with low downtime

Current situation is this: I have a large legacy DB using only MyISAM tables, the size of whole DB is 4GiB, one of the tables contains over 10 million rows, so it is pretty large. The database is ...
5
votes
2answers
10k 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 ...
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 ...
4
votes
1answer
201 views

Which MySQL storage engine is the most similar to ARCHIVE when that is not available?

I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
2
votes
0answers
292 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...