MyISAM is a non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. Also, is the default storage engine type for version prior to 5.5.
1
vote
2answers
2k 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
33 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?
1
vote
1answer
102 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
65 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
40 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
54 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
42 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
288 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
119 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
116 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 ...
1
vote
1answer
160 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
252 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 ...
1
vote
1answer
61 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
134 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 ...
1
vote
2answers
493 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?
1
vote
1answer
149 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 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
463 views
How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?
We have a keep-growing table which using MYISAM storage engine.
Almost every half year, this table will crash, and need to be repaired.
I checked the defragment result reported by windows ...
1
vote
1answer
134 views
MyISAM table row size is bigger than the sum of all the columns size?
I have a table:
CREATE TABLE `playlist` (
`pid` int(11) unsigned NOT NULL,
`mid` bigint(21) unsigned NOT NULL,
`status` tinyint(1) NOT NULL,
`add_time` int(11) unsigned NOT NULL,
...
1
vote
1answer
21 views
MyISAM key buffer
Good evening,
I want to know how can I use informations about MyISAM to calculate :
size of buffer,
size of buffer used,
Write ratio,
Read ratio,
and MyISAM key cache hit rate
and thank you.
1
vote
1answer
18 views
Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?
Per this post, in order to optimize the storage of a MyISAM table, you need to:
OPTIMIZE TABLE ;
ALTER TABLE ENGINE=MyISAM ;
ANALYZE TABLE ;
It's obvious what OPTIMIZE does for disk space but what ...
1
vote
0answers
41 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 ...
1
vote
2answers
120 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 ...
1
vote
0answers
75 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 ...
1
vote
1answer
76 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 ...
1
vote
2answers
554 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
0answers
71 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
1answer
630 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 ...
1
vote
0answers
142 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 ...
1
vote
0answers
88 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
778 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
1answer
936 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?
...
0
votes
1answer
423 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 ...
0
votes
3answers
35 views
Convert the table PROCESSLIST from InnoDB to MYISAM
I use MySQL. My database's name is "db", and I have a table called "Test".
I want recover the last update, so I used this query:
SELECT update_time
FROM information_schema.tables
WHERE ...
0
votes
1answer
85 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 ...
0
votes
1answer
75 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 ...
0
votes
1answer
115 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
...
0
votes
1answer
55 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` (
...
0
votes
1answer
64 views
MySQL Read Speed and Partitioning on Separate Drives
Introduction
Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
0
votes
1answer
50 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 ...
0
votes
1answer
468 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.
...
0
votes
2answers
83 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 ...
0
votes
2answers
236 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 ...
0
votes
1answer
230 views
Unable to change engine to MyISAM in MySQL
I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine.
I have checked following engines are supported in MySQL:
show engines\G
...
0
votes
1answer
221 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 ...
0
votes
2answers
408 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 ...
0
votes
1answer
415 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
376 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 ...
0
votes
2answers
722 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 ...
0
votes
1answer
80 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*' ...
