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
1answer
48 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
29 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
42 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
33 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
151 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
102 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
81 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
148 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
219 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
55 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
133 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
442 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
145 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
435 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
133 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
56 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
2answers
104 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
68 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
71 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
478 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
70 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
583 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
86 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
715 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
870 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
373 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
1answer
61 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
74 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
112 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
51 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
56 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
45 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
2answers
72 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
147 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
181 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
216 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
350 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
392 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
357 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
691 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
70 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*' ...
0
votes
1answer
92 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 ...
0
votes
1answer
14 views
Is the key_buffer_size applicable to myisam tmp tables?
I have a database about 750GB in size. It's all innodb.
Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The ...
0
votes
2answers
71 views
High Mysql Load , over 700% CPU
I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz ,
Alot of quiers in sending data mode
Here is mysql status
...
0
votes
0answers
39 views
MySQL “hangs” after every restart.. corrupted tables every now and then.. (5.1.68-cll)
I was seeking an advice in forums about a problem that we face last days. We have a vBulletin forum, we never had any problems in general, but the last couple of days MySQL server hangs and when we ...
0
votes
0answers
16 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
28 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,
...
0
votes
1answer
65 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.
...
