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

1
vote
2answers
28 views

Optimal engine for small lookup tables in MySQL

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...
0
votes
0answers
14 views

Confused over table_open_cache

I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables. I ran into an issue ...
0
votes
1answer
605 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 ...
1
vote
1answer
38 views

Delete and Recreate Indexes in MySQL to reduce table size for copying the db to a new server?

I have to move a given database of 100gb from server a to server b and later this year to server c with a copy for testing to server d. So all servers are Unix (mostly Debian) with MySQL. The ...
0
votes
0answers
8 views

How to backup huge Mysql db containing both InnoDB & MyISAM tables in Aws RDS?

I am running a Mysql RDS with 150GB+ data. The DB size is growing very fast(But normal considering the application's nature) Till now we were having only InnoDB storage engine, so we were using the ...
0
votes
2answers
42 views

Best configuration for MySQL? InnoDB and MyISAM

I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)... but results haven't turned ...
3
votes
1answer
229 views

MySQL fulltext selection performance

I have 1.5M rows in a table. Following is the table create code: CREATE TABLE `jobs` ( `id` INT(8) NOT NULL AUTO_INCREMENT, `job_id` VARCHAR(50) NOT NULL DEFAULT '', `title` VARCHAR(255) ...
1
vote
1answer
45 views

Why MySQL SELECT performance depends on table size? [closed]

I've got two identical tables with the following structure: CREATE TABLE words ( id bigint(20) NOT NULL, `text` int(11) NOT NULL, w1 int(11) NOT NULL, w2 int(11) NOT NULL, w3 int(11) NOT ...
1
vote
2answers
27 views

How to define partition according to Date?

I am currently using MySQL 5.6. In my database, I have a log table called paramlog_ems. It has 2M rows I want like to partition this log table according to Date. Here is the table structure: ...
1
vote
1answer
1k 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 ...
1
vote
1answer
324 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
1
vote
1answer
19 views

MySQL, can't find MYD files on OS X, MAMP

Try as I may I can't find the data files for my MAMP installation! FRM files are here: /Applications/MAMP/db/mysql/database_name/ but, there are no corresponding MYD or MYI files anywhere. The ...
2
votes
1answer
615 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
0
votes
1answer
30 views

Query databases that have MYISAM tables that do not have only fulltext index tables

I am trying to query from the information_schema where the database (table_schema) has MYISAM tables but does not have only fulltext index tables. The statistics tables is very poorly designed for ...
0
votes
1answer
847 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 ...
2
votes
2answers
29 views

MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically returned dataset is always the ...
1
vote
1answer
33 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
0
votes
1answer
360 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 ...
73
votes
9answers
66k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
0
votes
0answers
16 views

My.cnf tuning for a MySQL Slave [duplicate]

I have a MySQL Slave (MyISAM) where I only run SELECT statements. I would like to have a standard configuration in order to easily duplicate it on new slaves in the future. Currently the my.cnf is ...
2
votes
1answer
608 views

MySQL hangs in killed state

I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state. Scenario to reproduce: OS: CentOS 6.5 MySQL: 5.6 Engine: MyISAM (with at ...
0
votes
1answer
44 views

Query perfomance with multiple JOINs on MyISAM-based DB

I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign ...
2
votes
0answers
2k 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
21 views

Will 'sleeping' connections open files other than the socket in MySQL?

Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number ...
1
vote
1answer
236 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
69 views

Complex query performance in MySQL InnoDB versus MyISAM

I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database. My model: users <n---n> right_holders <n---n> tracks ---n> raw_detections I have this ...
2
votes
1answer
1k 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
3answers
3k 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
43 views

Heavy load causing MySQL Slave threads to remain “locked” or “Sending data” indefinitely

We have been rapidly adding MySQL servers as slaves to our master database server to help alleviate a massive increase in search (read-only) traffic to our web service. This past week, I was tasked ...
0
votes
0answers
49 views

During Peak hours memory utilization is too high?

I am using MySQL 5.1 and its memory size is 16GB,during peak hours it consuming complete memory only 200 mb of memory were free and My database size is only 500mb as well as no long running queries ...
4
votes
4answers
10k 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
69 views

Doubts about Converting MyISAM to InnoDB (master/slave and more)

I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process. Since I have a Master/Slave structure, do I need to manage this and change ...
5
votes
2answers
10k views

Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four. I am doing expensive joins on large tables, ...
1
vote
1answer
57 views

How to optimize a table/db with 200 insert/sec

I'm facing a very hard problem. I have a mysql DB (MyISAM) with a main table (user) where I write a row for every new user (around 10 fields). Every INSERT triggers around 10 new INSERT in 10 ...
0
votes
1answer
48 views

set global variable parameter?

My prod_server version is 5.1.47 and want to set the slow query log dynamically for certain period of time. I refered one site(http://toastergremlin.com/?p=276) there is mentioned below 5.1.6 we need ...
3
votes
2answers
204 views

How to speed up mysql export and import

I need to perform a DB migration from Server A to Server B. Server A: MySQL version installed 5.0.6 Server B: MySQL version installed 5.6.20 OS: Both are running RedHat Linux 64bit. 16 ...
1
vote
0answers
56 views

Suddenly high CPU usage

I've read a lot about MySQL optimisation and I already optimised MySQL about a year ago. Reason for that was a high system load average. I tuned many queries and edited the my.cnf file. Since then ...
0
votes
4answers
61 views

If all tables are myisam, should I use myisam engine instead of default innodb?

I have read all of the innodb/myisam threads and they are confusing. Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.? The software I am using creates ...
0
votes
2answers
31 views

Deleting a Large Crashed MyISAM Table

Our server has a large MyISAM MySQL table that is no longer needed and is currently crashed. If we repair it then it greatly slows down our server consuming sources and it's almost 50GB so the process ...
3
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
89 views

Invision Power Board 3.4.6 - mysql locks during query with pending updates

about 2 weeks ago, we started experiencing random lockups and slowdowns on our forums. We've been running this site for around 6 years minimum with no issues. the site and db are on dedicated vms. The ...
0
votes
1answer
41 views

Change Storage Engine on Referenced Tables

i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names ...
0
votes
2answers
25 views

Can I extrapolate from the progress reporting in MyISAM ALTER?

I'm migrating a 300 million row MyISAM table to add some indexes. Just because it might be relevant, here's my ALTER statement: ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ...
0
votes
1answer
90 views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
2
votes
2answers
847 views

How can I check if the keys are enabled on a MyISAM table?

I have a script, which populates 4 tables. It goes like this: Disable the keys on all the tables Populate the tables Enabled the keys back. The problem is that windows decided to reboot the ...
2
votes
2answers
173 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
0
votes
0answers
21 views

Default engine innodb problem

My MYSQL Version is 5.6.12 so the default engine should be InnoDB and is the same. I created few functions and procedures using MYSQL Query Browser but it went and got saved in a database called ...
0
votes
0answers
80 views

tmp_table_size and max_heap_table_size setting

Does anyone know whether or not setting global MYSQL variables(tmp_table_size and max_heap_table_size) to the same value will prevent Converting HEAP to MyISAM (since there should never be a temporary ...
1
vote
1answer
49 views

MySQL column type to boost performance

I'll have a table where: userid (link to user's id in users table) foldername (a varchar, max. 260 char) entry (varchar, max. 1024) data (varchar, max. 20kb) Single user will have several ...
1
vote
1answer
2k views

Strange MySQL replication error 1146 (Table doesn't exist)

I'm getting a very weird MySQL replication error No. 1146 for a REPLACE INTO query on a slave host replicating all tables in all databases from a master, and I'm having a bit of a hard time ...