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
1answer
128 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
4
votes
2answers
220 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
1answer
65 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 ...
1
vote
1answer
403 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
2answers
195 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 ...
1
vote
1answer
87 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
91 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
168 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 ...
0
votes
1answer
88 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 ...
2
votes
2answers
76 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 ...
2
votes
1answer
139 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
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 ...
0
votes
1answer
79 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 ...
0
votes
1answer
313 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 ...
0
votes
2answers
114 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
1answer
249 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 ...
1
vote
1answer
96 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 ...
1
vote
1answer
318 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 ...
4
votes
2answers
1k 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
268 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 ...
2
votes
4answers
251 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
1answer
96 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 ...
1
vote
2answers
207 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
2k 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 ...
0
votes
2answers
490 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 ...
0
votes
2answers
34 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
0answers
24 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 ...
2
votes
1answer
147 views

Only InnoDB databases locking up

I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation. At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up. ...
2
votes
1answer
2k views

Converting Heap to MyISAM issue

At work we have a problem that has now occurred more then once. Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill ...
2
votes
1answer
64 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 ...
0
votes
0answers
48 views

Slow queries while using views in MySQL

We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or select * from theView where user_id = 1234 kind of queries when searching. When I ...
0
votes
0answers
351 views

I have changed table engines from MyISAM to Aria, should I change the database engine too?

I have changed all tables in my mariadb database from MyISAM to Aria (using adminer as tool), but the database engine is still shown as InnoDB. Should I change this to Aria, too? I did not choose ...
2
votes
1answer
431 views

Need help in increasing maximum table size

Two of the tables vb_post, vb_profilevisitor getting corrupted again and again. The hosting company suggested the following: Looks like the problem is that the .MYD table was converted to a .TMD ...
2
votes
1answer
410 views

Will MyISAM prevent multiple users inserting data at the same time?

I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL ...
1
vote
1answer
6k 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 ...
0
votes
1answer
261 views

What does FTS_ file mean after converting from MyISAM to InnoDB?

I have convert some tables in database from MyISAM to InnoDB. After that, I see some modification on file system. The size of database is grown up, from 2.8GB to 4.8GB and there are many file with ...
0
votes
1answer
280 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
2
votes
2answers
698 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
1answer
343 views

moving binary database folder is causing issues with innodb tables

so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables ...
0
votes
1answer
62 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 ...
0
votes
0answers
854 views

Benchmarking MyISAM vs InnoDB using Sysbench on MariaDB-10.0.14

I performed a "read" benchmark on MariaDB-10.0.14 db server using sysbench to compare performance between InnoDB and MyISAM on a single. Below are the sysbench commands i used: Create test table in ...
2
votes
1answer
399 views

MySQL repair deleted data - Any chance of recovery?

Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was... mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at ...
0
votes
2answers
269 views

MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem ...
1
vote
0answers
229 views

“Unexpected EOF” during SELECT on huge MyISAM table

When executing a SELECT query that uses filesort on a huge table (2 billion rows) the execution stops after ~30mins with the following error message: ERROR 9 (HY000) at line 1: Unexpected EOF found ...
1
vote
1answer
481 views

MySQL backing up MyISAM tables by copying the physical files - and automating the process

I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly ...
1
vote
1answer
148 views

How do i increase the total MyISAM indexes?

I have just made a check for my server with mysqltuner. -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster ...
0
votes
1answer
149 views

Mysql - MyISAM Default order

We are using Mysql V5.6. We have a MyISAM table. when we open the table, the records will be displayed in some order instead of primary key order. Even when selecting the records will not be retrieved ...
1
vote
1answer
151 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
0answers
113 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
1
vote
1answer
40 views

Tables with no rows not empty

I've got a couple of tables that get cleared out regularly by a cron job on a web server. However when these tables are completely empty it looks like there is still data in them. e.g. This database ...