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

2
votes
1answer
1k views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
1
vote
2answers
482 views

MySQL views and table locking (5.1 MyISAM)

I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM. If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?
0
votes
1answer
66 views

Backup live MySQL database

I have a single MySQL database Server version: 5.5.35-cll MySQL Community Server (GPL) ENGINE is MyISAM I use 'mysqldump' to backup and restore the database. Size of the dumpfile is 3GB+ Once a ...
7
votes
2answers
928 views

How to drop empty tables

Is it possible to drop all empty tables from my huge database (mysql)? I'm looking for a sql command to automatically remove all those empty tables. Currently, I have 305 tables in my dataset, and ...
5
votes
1answer
2k views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
3
votes
1answer
384 views

Tuning key_reads in MySQL

We are trying to tune our MySQL database (Myisam, 5.1.73) which is getting busy. We investigated Key_reads: $ mysqladmin ext -ri10 | grep Key_reads During a normal load we have the following ...
0
votes
2answers
128 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
1answer
287 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
2answers
321 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
3
votes
3answers
638 views

MySQL Tables Require Daily Repairs - Server, Table or HD?

I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total. The bigger ones ...
1
vote
2answers
266 views

Why does MySQL + Windows Server = crashed tables periodically?

Windows Server 2008 R2 x64 MySQL Server 5.6.14 x64 Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by ...
2
votes
1answer
759 views

mysql: find column data size on disk

I have fairly large MyISAM table with longtext field in it Table size is somewhat 16GB. I need to know if clearing this column in old records helps to keep table small I can retrieve table wise ...
2
votes
1answer
81 views

How safe is it to widen a column in MySQL?

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot. A MySQL database I volunteered to help maintain has a table that looks like ...
0
votes
1answer
331 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
710 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
65 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
0answers
102 views

What are the steps to change tables engines from MyISAM to InnoDB?

I have a Datebase with 10MB size . The Tables' engines were MyISAM (table-lock level). So I deiced to change them to InnoDB (row-lock level). I used the sql query for each table : ALTER TABLE t ...
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 ...
1
vote
1answer
145 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
0
votes
2answers
74 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 ...
1
vote
2answers
819 views

Setting default CHARSET and DB Engine

I recently updated my server's database system from MySQL to MariaDB. After update, everything works well. But, when I went to phpMyAdmin, the default charset and types are changed: At the end of ...
1
vote
1answer
623 views

Mysql Performance tuning for myisam tables.

Folks I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records ...
1
vote
1answer
1k views

how to set bulk_insert_buffer_size in mysql?

I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such ...
1
vote
1answer
838 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
3answers
805 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
228 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
487 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 ...
2
votes
1answer
465 views

Is it safe to kill ANALYZE TABLE query on a MyISAM table?

I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an ...
4
votes
1answer
3k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
0
votes
0answers
223 views

Improve Speed of MySQL Database Import

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 ...
2
votes
1answer
355 views

Issue moving database between servers (cannot find .frm file even though it's there)

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great. The name of my database is roster and it has one table called users. ...
3
votes
1answer
136 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
1
vote
1answer
93 views

MySQL command querying all MyISAM database

I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
2
votes
2answers
821 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
694 views

Spatial Index on an InnoDB table

I have a database which is in InnoDB, because I wanted consistency (cascading delete). Now, I want to use spatial indexes for selecting rows ordered by geometric distance. However, spatial indexes ...
1
vote
1answer
313 views

MyISAM vs InnoDB for Forums [closed]

I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
3
votes
2answers
819 views

How to backup a MySQL database without slowing the application?

I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
2
votes
1answer
64 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
1
vote
1answer
2k views

Merge multiple tables in a database

I have a MySQL database with table names like 1,2,3... upto 1000 Each tables have 2000 rows and has the same structure. This is how all my 2000 table's structure look like id,url,title,content id ...
3
votes
2answers
4k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
0
votes
1answer
43 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, ...
3
votes
1answer
234 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
0
votes
1answer
76 views

MyISAM table is not working as expected in MySQL

mysql>show processlist; +-------+------+------------------------------+---------------------+---------+-------+-------+------------------+ | Id | User | Host | db ...
1
vote
2answers
2k views

Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables. Can I now remove the ibdata1 file completely?
2
votes
1answer
198 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 ...
1
vote
1answer
94 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
0
votes
1answer
284 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
258 views

Latin1 to UTF8 on large MySQL database, minimal downtime

Let me lay out the basic scenario for the problem I'm up against. I'm running MySQL 5.1 and have a single database with 2 large MyISAM tables (9M and 20M rows respectively). Each of these tables has ...
1
vote
2answers
2k 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 ...
1
vote
1answer
354 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.