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

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
2
votes
2answers
501 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 ...
5
votes
1answer
769 views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
2
votes
1answer
115 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 ...
1
vote
0answers
173 views

How to speed up this query

MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
3
votes
4answers
5k 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 ...
1
vote
2answers
481 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
243 views

extremely slow MyISAM slave updates

I have 2 slaves and one is working as expected. The other slave is extermely slow while executing the "update" statements. The insert and deletes are fast. I used "check table tbl_name" to see if any ...
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, ...
3
votes
1answer
2k 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
2answers
318 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 ...
0
votes
1answer
670 views

How to restore mysql from only myi files?

our server hdds has crashed and we didnt have a current backup so we sent our hdds to a data recovery company, they have been working on hdd's for over a week and they have finally managed to save ...
12
votes
2answers
3k views

Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down. Now that the ...
10
votes
1answer
2k views

MySQL table with 100,000 records queried often

I have a single database of about 100 tables to store various kinds of information. The most important table is our order table which is used to store customers orders and is over 100000 records as ...
4
votes
3answers
2k 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
865 views

Which mysql storage engine to choose?

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB). The theories say: Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
-2
votes
1answer
2k views

Why does MATCH (FullTextSearch) AGAINST ('*' IN BOOLEAN MODE) return nothing?

MATCH (FullTextSearch) AGAINST ('k*' IN BOOLEAN MODE) return something Namely anything with a word that start with k MATCH (FullTextSearch) AGAINST ('ku*' IN BOOLEAN MODE) return less MATCH ...
1
vote
0answers
107 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE ...
1
vote
1answer
175 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 ...
3
votes
2answers
604 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 ...
0
votes
1answer
101 views

InnoDB possible error

We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB ...
19
votes
3answers
36k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
2
votes
5answers
9k 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
4k 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 ...
6
votes
2answers
2k views

BigInt or Varchar for a large number I won't ever sort / calculate on

I need to store users' Facebook IDs in a column in my DB. The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do ...
1
vote
1answer
2k views

MySQL MyISAM Locking question

It was my understanding that MyISAM uses table locking during inserts and updates. To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
3
votes
3answers
337 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 .... ...
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
618 views

How can I check if the keys are enabled on a MyISAM table?

I have a script, which populates 4 tables. It goes like this: Disable the keys on all the tables Populate the tables Enabled the keys back. The problem is that windows decided to reboot the ...
4
votes
2answers
4k views

Performance settings for MyISAM tables - keep everything in memory

I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data). Since the databases size is several times smaller than the available memory, how can I make full usage ...
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 ...
2
votes
1answer
310 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 ...
4
votes
3answers
1k views

MySQL tables/databases into different disks

I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks. Lets say you have 5 ...
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 ...
4
votes
1answer
1k 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 ...
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 ...
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 ...
1
vote
2answers
484 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 ...
4
votes
2answers
2k 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 ...
6
votes
2answers
2k views

Any gotchas at all with converting from MyISAM to InnoDB?

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
1
vote
1answer
2k views

Write lock during mysqldump even using --lock-tables=false

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this? As I am using MyISAM, does --single-transaction for InnoDB help? ...
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
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? ...
2
votes
2answers
663 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
482 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 ...
3
votes
2answers
647 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 ...
1
vote
1answer
315 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
5k 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 ...
3
votes
2answers
933 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 ...
5
votes
3answers
353 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON ...