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.
2
votes
3answers
1k views
MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine
I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.
I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
1
vote
1answer
199 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
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
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 ...
4
votes
1answer
130 views
Which MySQL storage engine is the most similar to ARCHIVE when that is not available?
I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
6
votes
1answer
228 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
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 ...
4
votes
1answer
261 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 ...
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 ...
0
votes
0answers
61 views
Unable to set table-specific privileges
I want to give a user access to only a few tables. I execute this query:
GRANT SELECT (credits_features_csgo, steam_code, credits_csgo, userid,
credits_forum, credits_features_tf2, credits_tf2, ...
2
votes
1answer
349 views
mysqlcheck to optimize innodb tables — performance/scheduling
I'm attempting to optimize tables on a very large, production database server. I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.
Is there a ...
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?
3
votes
4answers
329 views
TRUNCATE TABLE statement sometimes hangs
Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?
I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.
...
3
votes
2answers
116 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
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
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 ...
5
votes
2answers
423 views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
3
votes
1answer
1k 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 ...
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
2answers
170 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 ...
4
votes
1answer
127 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 < ...
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 ...
1
vote
1answer
80 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) ...
3
votes
1answer
401 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
2
votes
1answer
193 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 ...
0
votes
1answer
422 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 ...
1
vote
1answer
123 views
Optimizing table and choosing storage engine
Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
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. ...
2
votes
1answer
359 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 ...
6
votes
1answer
2k 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 ...
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 ...
5
votes
1answer
378 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
95 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 ...
2
votes
2answers
654 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 ...
1
vote
2answers
230 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 ...
3
votes
4answers
2k 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
...
2
votes
1answer
928 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
656 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
226 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 ...
4
votes
2answers
5k views
way to prevent queries from waiting for table level lock
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
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 ...
9
votes
1answer
928 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 ...
3
votes
3answers
639 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 ...
0
votes
0answers
50 views
Spatial Database over smaller rectangle takes longer
I have to be very careful here. Both queries are so similar that the only different is the area searched.
What's surprising is the query that run on BIGGER AREA, and naturally yield MORE ROWS, is the ...
0
votes
2answers
720 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 ...
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 ...
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
...
-2
votes
1answer
841 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
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
...
