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

1
vote
1answer
229 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
6
votes
1answer
645 views

LOAD DATA INFILE blocks on concurrent use against MyISAM tables

I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
1
vote
2answers
2k 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 ...
4
votes
1answer
1k views

How much does table size affect performance?

Lets say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often. By not regularly archiving or deleting these ...
4
votes
1answer
181 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 < ...
1
vote
1answer
109 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?
0
votes
1answer
120 views

myISAM data loss at DELETE / INSERT

I have a linked table id_group | id_user. Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the ...
2
votes
1answer
348 views

myisampack running really slow for large DBs

I was going through compressing a bunch of archival myisam tables using myisampack It was making decent progress until it got to the most recent tables. The overall database is a set of archives for ...
0
votes
2answers
1k views

Some queries slower in MySQL 5.5 than 5.0?

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran. We recently updated ...
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 ...
0
votes
2answers
2k views

MySQL how to release memory used up by SELECT

I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C. INSERT IGNORE INTO sid(sid) SELECT sid ...
1
vote
0answers
135 views

How can I force myisamchk to modify the original data file in case of duplicate keys?

In the documentation for myisamchk, it says: --quick, -q Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to ...
1
vote
2answers
186 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 ...
6
votes
1answer
4k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
5
votes
2answers
9k views

Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four. I am doing expensive joins on large tables, ...
1
vote
1answer
97 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
154 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
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 ...
2
votes
1answer
335 views

MySQL table locks solution -> InnoDb / Partitions

I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows). It's mainly caused due many heavy inserts. My current thoughts to optimize: Changing to ...
1
vote
1answer
168 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 ...
4
votes
2answers
621 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 ...
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 ...
0
votes
1answer
2k views

Unable to select the records from specific partition in mysql?

I've created a table using the following query, CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id ...
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
3k 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
1answer
2k 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
554 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
936 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
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 ...
1
vote
0answers
185 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
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 ...
1
vote
2answers
543 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
271 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
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
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 ...
0
votes
1answer
751 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
3k 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
1k 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
3k 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
188 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
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 ...
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 ...
23
votes
3answers
42k 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?
4
votes
5answers
10k 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 ...
7
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 ...