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.
6
votes
1answer
216 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
225 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
221 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
484 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
317 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
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?
3
votes
2answers
115 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
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
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 ...
5
votes
2answers
387 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
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
2answers
168 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
124 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
354 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
78 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
341 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
187 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
378 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
120 views
Optimizing table and choosing storage engine
Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
0
votes
1answer
394 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
344 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
586 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
344 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
91 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
580 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
218 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
834 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
582 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
214 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
360 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
835 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
551 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
694 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
113 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
774 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
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
...
0
votes
1answer
71 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
0answers
139 views
Nearest neighbor search for 1.6 million points in myisam mysql
Say I want to find 20 closest business near me. Latter I want to see the next 20 closest, etc. Yes I have myisam. I have spatial indexes on the point. I am looking for actual command.
Note:
I am ...
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
449 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?
3
votes
2answers
307 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
0answers
633 views
Generating an ER diagram with relationships from an MySQL MyISAM database?
Are they any tools to extract out the relationships in a MyISAM database into an ER diagram?
I understand that MyISAM doesn't have foreign keys, but the columns in my database that represent foreign ...
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 ...
