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
44 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. ...
1
vote
0answers
227 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
0
votes
0answers
86 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 ...
2
votes
1answer
2k views

Unique insert performance: Primary Key vs Unique Index

I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records. What's the fastest way to insert into the table, by keeping the domain unique ...
0
votes
2answers
62 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
4
votes
2answers
8k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
0
votes
1answer
259 views

Queries are taking long time to execute

I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine. Problem started 3 days back, there is long list ...
2
votes
1answer
122 views

mysql Wrong bytesec: 0-0-0 at linkstart: 0

I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns: > +-----------------+-------+----------+----------------------------------- | > ...
2
votes
1answer
3k views

mysql table very large size although it doesn't have a lot of records

I have drupal website, one of the tables is cache_form. I have a cron to DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW()); which works fine. The size of cache_form.myd is 4.9G ...
0
votes
0answers
110 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, ...
1
vote
1answer
76 views

Concurrency Issues when multiple users uses Portal (MySQL and PHP)

We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no ...
1
vote
1answer
115 views

Mysql sequential numbering based on MAX of another column

I've a table lp_pictures which have these fields: [id | plate_uid] [1 | aaa] [1 | aaa] [1 | aaa] [1 | bbb] [1 | bbb] [1 | bbb] and i need to UPDATE the field id to becomes: [id | plate_uid] ...
2
votes
1answer
159 views

Does MySQL guarantee atomic upgrade from read lock to write lock?

If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the ...
2
votes
1answer
199 views

MySQL Error 1114 “table is full” Using MyISAM Engine

When executing the following query, we run into the error The table '/var/lib/mysql/temp/#sql_2d22_1' is full (actual table name varies). I've done a fair amount of research on this; most of the ...
8
votes
2answers
275 views

Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
1
vote
1answer
69 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
68
votes
8answers
53k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
1
vote
2answers
273 views

Why is MySQL not using the index with the higher cardinality?

I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
2
votes
1answer
574 views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
1
vote
1answer
173 views

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
0
votes
0answers
288 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 ...
13
votes
3answers
4k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
0
votes
2answers
56 views

MySQL query optimization for legacy application

I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone. ...
0
votes
1answer
54 views

Fetching User Recent Status

I am running mysql with MyISAM storage engine to store user status(user updates data). To load user recent updates data i am using this query: $limit = 20; $uid = 3; SELECT * FROM userstatus WHERE ...
0
votes
0answers
45 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 ...
5
votes
1answer
777 views

Quick MySQL Backup (1 file per table)

I asked a relevant question few months back and got answer with wide variety of options for backup. But this time I have different scenario and I have got a database with 7 tables and 1 of them has ...
3
votes
2answers
735 views

Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for ...
2
votes
0answers
1k views

MySQL - high CPU usage (MyISAM tables)

We have a server running LAMP stack on CentOS release 5.6 (Final) MySQL is having high CPU usage constantly which causes server to lag as shown top - 13:50:47 up 80 days, 13:53, 1 user, load ...
0
votes
1answer
135 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 ...
1
vote
1answer
908 views

mysqldump: Does --lock-tables=false work with MyISAM tables?

So, I have to check and come with a backup solution for a very busy mysql server (with tables over 5 GB). Due to resources constraints, they choose to set all tables with the MyISAM engine. Currently, ...
0
votes
1answer
170 views

Best way to add a new column to a large table? (MySQL - MyISAM)

Here's more info on the table that needs to be altered: 15GB size MyISAM table with Index around 5GB Over 100 million rows MySQL 5.1 on RHEL 5 with 4GB RAM Currently live online What's the best ...
0
votes
1answer
8k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
1
vote
1answer
140 views

InnoDB to MyISAM Conversion Problem

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value ...
2
votes
2answers
258 views

Why does MySQL fail to use an index effectively when LIMIT offset is large?

I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec: SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5; But querying ...
0
votes
0answers
323 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 ...
1
vote
1answer
333 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
1
vote
1answer
937 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
415 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
62 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
736 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
275 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
109 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
223 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
303 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
557 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
227 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 ...
0
votes
0answers
26 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
107 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 ...
2
votes
1answer
711 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 ...