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
1answer
87 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, because in our current setup it takes ...
2
votes
1answer
1k 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 ...
2
votes
1answer
2k 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, ...
2
votes
2answers
136 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 ...
2
votes
2answers
446 views

MyISAM table keeps crashing. What are my options?

My MYSQL table is very large (250GB, around 200M rows). Recently I've been putting it through a lot -- parallel processes that do a large number of reads. It has started locking and crashing with ...
2
votes
1answer
1k views

importing myisam 5.0 database into a 5.5 innodb server

I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
1
vote
5answers
7k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
1
vote
1answer
207 views

MyISAM vs InnoDB for Forums [closed]

I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
1
vote
2answers
62 views

Why does MySQL slow down in loading data?

We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files. What's confusing is that the first file took only 2 minutes to load, whereas the ...
1
vote
2answers
182 views

What MySQL storage engine should I choose?

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows. What engine should I choose for my database, InnoDB or MyISAM? There will only be about 1,000 new records ...
1
vote
1answer
286 views

Why did the MySQL Storage Engine Change on Restore in one scenario?

In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all ...
1
vote
2answers
67 views

MySQL views and table locking (5.1 MyISAM)

I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM. If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?
1
vote
2answers
427 views

Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables. Can I now remove the ibdata1 file completely?
1
vote
1answer
214 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
98 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
374 views

MySQL: reducing ibdata file size for MyISAM tables

My Question is actually very similar to this one and also includes a good answer for a case with InnoDB Engine tables: Is there any best way to reduce the size of ibdata in mysql.?, I have noticed ...
1
vote
1answer
147 views

How to interpret explain sql?

SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.293413 * SQRT( pow( - 6.185 - TB.Latitude, 2 ) + pow( 106.773 - TB.Longitude, 2 ) * 0.988392289802 ) AS Distance FROM `tablebusiness` AS TB ...
1
vote
1answer
3k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
1
vote
1answer
100 views

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
1
vote
1answer
72 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
1
vote
1answer
1k views

Configuring MySQL (my.cnf) for MyISAM and InnoDB

I want to convert some (not all) the MyISAM tables to InnoDB. Here is my current my.cnf: pastebin.com/FW8YXtLQ What would be the best configuration? (need to add InnoDB settings, byecause there are ...
1
vote
1answer
325 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
180 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 ...
1
vote
1answer
665 views

mysql optimize table crash

When I try OPTIMIZE TABLE `table` (MyISAM) on a table which is about 300MB, then it is crashed and must be repaired. What could cause this problem? The same problem occurs on other tables over 300MB. ...
1
vote
1answer
141 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 ...
1
vote
1answer
112 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 ...
1
vote
1answer
466 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 ...
1
vote
1answer
91 views

Select ``,* from table?

I'm trying to upgrade our database servers to MySQL 5.5. In doing so, I'd like to convert all of our tables to InnoDB to take advantage of several performance benefits. However, we use VARCHAR(50) ...
1
vote
1answer
148 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
2answers
385 views

All columns of table should be indexed or not mysql database?

I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins ...
1
vote
2answers
4k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
1
vote
1answer
56 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 ...
1
vote
2answers
78 views

Why does MySQL + Windows Server = crashed tables periodically?

Windows Server 2008 R2 x64 MySQL Server 5.6.14 x64 Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by ...
1
vote
1answer
259 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
1answer
98 views

Mysql Performance tuning for myisam tables.

Folks I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records ...
1
vote
1answer
44 views

MySQL command querying all MyISAM database

I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
1
vote
1answer
347 views

Spatial Index on an InnoDB table

I have a database which is in InnoDB, because I wanted consistency (cascading delete). Now, I want to use spatial indexes for selecting rows ordered by geometric distance. However, spatial indexes ...
1
vote
1answer
306 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 ...
1
vote
1answer
63 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
1
vote
1answer
160 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`; ...
1
vote
1answer
192 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
76 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
2k 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 ...
1
vote
1answer
229 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?
1
vote
1answer
418 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 ...
1
vote
1answer
215 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 ...
1
vote
2answers
1k views

MySQL not responding, Writing to net status

I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB. Here's the show ...
1
vote
1answer
93 views

How can I reinitialize some MyISAM tables without restarting MySQL?

I'm using rsync to copy some measurement data from one PC to the server. Is it possible to load the new MyISAM tables without restarting the MySQL server?
1
vote
1answer
611 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. ...
1
vote
1answer
163 views

What is the SQL command to get 20 closest points from some center location

There are 1.6 million rows. Must not compute distance for all rows. Spatial index is available This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added ...