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

0
votes
1answer
115 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
2answers
1k 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?
0
votes
1answer
47 views

Why is my Mysql Query verry slow when selecting a text field

i have a problem with a left join. Tables are myisam. Tables CREATE TABLE `article` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `force_startpage` tinyint(1) DEFAULT '0', `title` varchar(255) ...
0
votes
1answer
55 views

How to transfer 70GB MySQL with no downtime?

I have a large amount (4000) of individual databases, 90% of which are small. They all include about 20 tables each, and in total, they use about 75GB of space. All tables are MyISAM. I want to ...
0
votes
1answer
145 views

Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
0
votes
1answer
365 views

My database keeps crashing on startup how can i repair it without it running?

I've got some problems with my database. I hope somebody can help me out. Problem: The database keeps crashing on startup. In my log i see a couple off these errors: [ERROR] /usr/libexec/mysqld: ...
0
votes
1answer
40 views

is it possible to set engine for database (not the tables)?

There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server. They were imported and were displayed in the tree structure of phpmyadmin (under the ...
0
votes
1answer
59 views

Few Insert queries rollbacked due to system crash

Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked. I am sure that they were proper ...
0
votes
2answers
59 views

MySQL using swap during conversion to InnoDB

I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions ...
0
votes
2answers
94 views

Should we explicitly lock MyISAM tables or is it implicit…?

Does MyISAM tables automatically lock a table for each select or insert statement .... I mean if I query the database as select * from tablename then does the MySQL engine first lock it for read ...
0
votes
3answers
504 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
151 views

Backing up MySQL w/MyISAM on Amazon RDS

So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS. I've just taken over this application, and I'm concerned we're not getting good backups, since ...
0
votes
1answer
108 views

When and how would MyISAM concurrent insert = always be a problem?

I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table. We're talking about 10-20 inserts ...
0
votes
1answer
265 views

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found. Commands ran: CREATE ...
0
votes
1answer
66 views

MyISAM table is not working as expected in MySQL

mysql>show processlist; +-------+------+------------------------------+---------------------+---------+-------+-------+------------------+ | Id | User | Host | db ...
0
votes
2answers
136 views

MySQL database datafiles in windows SystemData directory

When i create a database and some tables in MySQL Server, it makes some data files in Windows SystemData directory in C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\. Each table has three files with ...
0
votes
1answer
252 views

Information about Disk Storage MySQL

I am trying to create an application for monitoring MySQL,in my application there's a part of monitor DISK STORAGE, so after more research I have not found how to calculate : used and free size of ...
0
votes
1answer
100 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
345 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
273 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
453 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
1k 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
2answers
1k 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
42 views

Which text-index I should create for xooops engine to achieve better search results?

In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following : SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
0
votes
1answer
387 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
1k 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
674 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
974 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
222 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
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 ...
0
votes
1answer
20 views

I'm building up a large MyISAM table from a few dumps. The first 21GB took less than 10 hrs total. The next 5GB is forecasted to take two months

I had a couple machines building up identical MyISAM tables. I've dumped all of those tables to a few files, and am now trying to restore them all into a single table on my own machine. The first ...
0
votes
0answers
30 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
0
votes
2answers
45 views

Alter auto-increment of table with 16 million rows

Learned this the hard way. Need to alter a table's auto-increment with 16 million rows. Now, it's taking quite sometime. Any estimate how long it would take? Sorry.
0
votes
0answers
87 views

mysqlcheck --repair a specific table?

I logged in my server via SSH and committed a "repair table xxx" query on a MyISAM table with 34 million rows and 69GB of data. Unfortunately the SSH connection was lost. When I logged back in I found ...
0
votes
0answers
12 views

What is data like *after* fixing a corrupt MyISAM or InnoDB table?

I collect user data and store in an a database. Some of the tables are MyISAM and some are InnoDB. Lately, both have been experiencing regular corruption for reasons not clear. I know that restoring ...
0
votes
0answers
19 views

How do I stop views (large data sets) from freezing my database

I am working with tables that contain upwards of 200,000 rows. My boss wants to use views that refer to other views in order to have things be more automated. The problem is that the MySQL server ...
0
votes
1answer
37 views

MyISAM InnoDB performance

MyISAM vs InnoDB I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself. When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver. ...
0
votes
0answers
52 views

What is a good database design (schema) for an article search engine application?

I'm trying to make a article search engine application. There are about 1 million articles that will be stored in the DB. I've tried looking around for some similar examples, but I couldn't find any ...
0
votes
1answer
174 views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
0
votes
0answers
71 views

MySQL: after query optimization the CPU is still over 100%

my first time here! First of all I want to say that I'm not a DBA and I have a very little experience with MySQL but I like to study and understand what I'm doing. I have a virtual machine (2gb RAM, ...
0
votes
1answer
55 views

MyISAM : Should I be concerned by these warnings?

I restored some tables from recent Databases backups. Because Im supporting a legacy system that only uses MyISAM tables in their DBs, for backing up I used a little script that pretty much copies all ...
0
votes
0answers
188 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
0
votes
1answer
39 views

MyISAM recover updated row

The row (with type MEDIUMTEXT) has been accidentally updated with empty value. I can see previous data in MYD file of a table, but I can't get them neither from mysqldump or any mysql client. If I ...
0
votes
0answers
40 views

Quick MySQL Backup (1 file per table) - v2

Improving bash shell script written by RolandoMySQLDBA. Continuing the following thread: Quick MySQL Backup (1 file per table) I tried to adapt your script, but after the first rsync was done, I get ...
0
votes
2answers
554 views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
0
votes
0answers
192 views

How to recover a crashed MyISAM table which myisamchk couldn't recover?

We take db backup by stopping the database and then issuing a tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error ...
0
votes
1answer
109 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
0
votes
1answer
264 views

INSERT take too much time

I have in my MySQL DB a few tables Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections ...
0
votes
0answers
24 views

How to change a table's data directory

I tried to modify with ALTER TABLE, and as I found from the official documentation: ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options. What other options do I have? ...
0
votes
0answers
81 views

.myd and .myi versus .frm and .ibd

I'm new to MySQL.I have trouble finding the .frm files and .ibd files in Windows 8 Explorer.They seem hidden and I have to find them using 'Find' and 'Open File Location', not sure how to make these ...