Questions tagged [myisam]

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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
39 views

MyISAM table repair stuck in loop

my MyISAM table is broken (I know it's better to use Innodb), but now I need to fix this large table. 200GB I have already tried the following options, but the table repair seems endless and the TMD ...
serguei's user avatar
  • 21
1 vote
0 answers
41 views

Wrong query optimization plan with InnoDB

After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage. The problem are some queries that are wrong optimized with the new engine. ...
LateAtNight's user avatar
0 votes
2 answers
462 views

MySQL's maximum memory usage is dangerously high and CPU run 100%

Hi we faced an issue on our server and it go verry slow for more tan 1 month and it go worst every day. I am really not server administrator, This is mysqltuner.pl result, -------- Storage Engine ...
Mathieu's user avatar
0 votes
1 answer
33 views

Mysql MYISAM force operating on a crashed database

Is theres a way to forcibly operate on a database that crashed (even if it's not possible through mysql cli at least a way to remove the dirty bit that marks it as crashed by editing with an editor). ...
user avatar
1 vote
1 answer
183 views

Migrate MyISAM tables to InnoDB using replication to avoid a long down time

We have to migrate MyISAM tables to InnoDB but we are afread of a long down time. In our database we have 10 MyISAM tables where 2 of them are the biggest: ~26GB data + ~10GB indexes. My question is: ...
Ela's user avatar
  • 500
2 votes
1 answer
59 views

MariaDB counting matching rows in table ~50m rows

Running MariaDB 10.9.4 on Debian 11, I have a table of flights with approximately 50 million rows My structure looks like: id int(11) auto increment flight_number varchar(16) aircraft char(6) ...
James W's user avatar
  • 41
0 votes
1 answer
162 views

MySQL update with inner join taking too long

I have the following two tables CREATE TABLE `tbl_products_temp` ( `cd_id` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, `cd_structure` MEDIUMBLOB NOT NULL, `cd_smiles` TEXT NULL ...
adam78's user avatar
  • 155
0 votes
1 answer
222 views

LVM snapshot of MyISAM tables requires repair every time

I'm attempting to setup slave replication of a large MyISAM table. I login to the master server and perform FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS and that returns right away with the ...
basedrum1's user avatar
0 votes
1 answer
891 views

How to repair MyISAM table?

I'm trying to recover a corrupted MyISAM table from a legacy system. The table is corrupted when there is a sudden power loss. It has little over 500 million rows and is 23GB is size (.MYD file) ...
CodeBreaker's user avatar
1 vote
1 answer
82 views

How to change the engline type from InnoDb to MyISAM of an existing db?

I have a MariaDb database which consists of 2 tables and which is around 10Gb of the size. One is dependendant on the other via a foreign key. How can I change the engine type of that particular db ...
Kum's user avatar
  • 47
0 votes
1 answer
47 views

wrong index in composite PRIMARY KEY in MyISAM

I use MyISAM for processing tables, as it is much faster to read large data from MyISAM as compared with InnoDB for JOINs. I create map tables for bulk INSERT, CREATE TABLE tag_map ( article_id int(11)...
Googlebot's user avatar
  • 4,521
1 vote
2 answers
193 views

In MySQL, is neccesary or recommended a LOCK TABLE before a REPAIR TABLE

I need to know if is neccesary or recommended to try a LOCK TABLE before a REPAIR TABLE, because sometimes we need to make REPAIR of tables when users are working on the application. As I have tested, ...
Keos's user avatar
  • 13
0 votes
1 answer
50 views

Split huge (sales) table in MySQL int smaller tables in order to speed up crash recovery?

So I have to maintain a legacy system that has millions of rows in a MySQL myisam table. Once in a blue moon, the UPS or the power supply craps out, and crashes the server and hence the tables will be ...
Moritz von Schweinitz's user avatar
1 vote
2 answers
2k views

Default 0000-00-00 00:00:00 DateTime values in InnoDB

I have changed the MyISAM tables to InnoDB. During the conversion, I changed SQL_MODE (SET SQL_MODE='ALLOW_INVALID_DATES';) because in some tables I had DateTime records with the default value of '...
Fariman Kashani's user avatar
0 votes
0 answers
115 views

MySQL: Migrate MyISAM table with multicolumn primary index with autoincrement to InnoDB

I have a large (10M rows) MyISAM table with a primary index on 2 different columns where one of them is auto_increment. How can I migrate it to InnoDB in MySQL 5.7? The table is something like: CREATE ...
Luca Leen's user avatar
0 votes
1 answer
500 views

MySQL queries still slow after deleting bunch of records

I need some help with resolving some mysql issues. My website had been running slow since last week and after contacting my host I found out that some queries were taking too long mainly because of ...
sayayin's user avatar
  • 103
0 votes
1 answer
500 views

Mariadb concurrent insert not working for MYISAM tables

I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts. Recently I had to add a new 'SELECT' query to the ...
atm007's user avatar
  • 1
0 votes
2 answers
302 views

MySQL QPS debugging?

I see 5 Kqps on my DB server during 5 mins interval on zabbix dashboard. But sum of all max queries(Delete/select/Insert/Update/Replace) during that interval come out to be 1300. Not sure how come max ...
user3198603's user avatar
3 votes
1 answer
15k views

mysqldump with --no-tablespaces on InnoDB with index?

Does mysqldump --no-tablespaces dumps index files of InnoDB? I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
Aldee's user avatar
  • 41
0 votes
1 answer
130 views

Should I use MyISAM over InnoDB to save disk space?

I have a table that consists of an int primary key and text column and serving primarily in a "read" role. There are currently only about 40,000 rows, but in time, I expect there to be an ...
pbarney's user avatar
  • 161
0 votes
2 answers
416 views

in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?

I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload. Based on this What are the main differences between InnoDB and MyISAM? When ...
Ido Shilon's user avatar
2 votes
2 answers
335 views

Why MySQL performs so poor on SHOW PROCEDURE STATUS?

I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I ...
Ehsan Khodarahmi's user avatar
2 votes
1 answer
14k views

MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables: 2020-10-17 0:51:18 ...
LOM's user avatar
  • 21
1 vote
1 answer
881 views

Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column?

First, the background on how I discovered this issue. This happened when using MySQL 5.7 and the DB — as well as the related table — are using MyISAM. I was debugging some code — and related MySQL ...
Giacomo1968's user avatar
1 vote
4 answers
397 views

Load index to cache is not using memory available in the key_buffer_size variable

Good afternoon folks, I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading ...
alexandredelcol's user avatar
1 vote
1 answer
257 views

Issues with MySQL 8.0.17

Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18. We are facing issues with our web application, Website is loading slowly if there are more users accessing it I ...
Vishal's user avatar
  • 119
1 vote
1 answer
323 views

What is branching factor of mysql myisam's b-tree

I am doing some research but I am struggling to find what is branching factor of MYISAM's b-tree, e.g. how many keys per page (node). I also would like to know how the char/varchar keys are stored - ...
Nick's user avatar
  • 141
1 vote
2 answers
1k views

After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour?

I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=...
mhweb's user avatar
  • 63
1 vote
1 answer
388 views

MySQL InnoDB Insertion Faster Than MyISAM

I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion. MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf. And tables ...
Bruce's user avatar
  • 21
0 votes
1 answer
41 views

Join on large table Myisam & Mysql 5.0

I searched on some forums, perconna and more but i didn't find answers. We have a legacy server with Mysql 5.0 and some tables on Myisam. I'm trying to optimize my request. We have 3 tables mysql&...
Thomas Corbisier's user avatar
1 vote
3 answers
193 views

MyISAM around 5x slower than InnoDB

I thought, MyISAM is for simple tables and selects due to its architecture faster than InnoDB. Therefore I changed the engine for this table from InnoDB to MyISAM: CREATE TABLE `table1` ( `DateTime`...
Peter VARGA's user avatar
0 votes
1 answer
266 views

How to fix conversion error from InnoDB to MyISAM?

How can I fix conversion error from InnoDB to MyISAM for these tables? MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is ...
execash's user avatar
0 votes
1 answer
346 views

Changing storage engine to InnoDb from MyIsam is dropping records from table

Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the ...
SohaIb AhmEd's user avatar
-1 votes
1 answer
77 views

Using xtrabackup backup to recover MyISAM data

Why do I have a ibdata1 file when backing up MyISAM data? I don't have a InnoDB table. I can't find anything about recovering MyISAM data on the official website. How Do Irestore a single backup of ...
cosmos's user avatar
  • 1
0 votes
1 answer
63 views

Mysql separate username from client table for performance and use myisam for it

I'm creating a database for a very heavy messenger like Telegram ... I decided to use a different table for client-username and separate it from the clients table. This is My 'client' table: InnoDB ...
Mehdi's user avatar
  • 3
0 votes
2 answers
5k views

MySQL - change all tables' ENGINE from MyISAM to InnoDB with a single query

I had some foreign key issues in my database when I change the table engine "MyISAM" to "InnoDB" but then it works fine. I found one query to change a single table's engine but I have many tables so ...
always-a-learner's user avatar
1 vote
3 answers
430 views

From MySQL to MariaDB : bad performances issue with MyISAM tables

I'm currently migrating a Debian 6 MySQL server (5.1.73) to a fresh new Debian 10 MariaDB server (10.3.18). Please note that I'm not a database expert. All tables are under MyISAM engine storage, for ...
Sicaa's user avatar
  • 11
1 vote
1 answer
241 views

troubleshooting mysqlimport very large text file

I've been attempting to import a very large (390 GiB) text file using the mysqlimport client. I've been successful at importing a 14 GiB text file as well as numerous smaller files using this method. ...
haz's user avatar
  • 61
1 vote
3 answers
10k views

Disk full of mysql .mad and .mai files

On a (owned) shared server sometimes mysql writes alott of .mad and .mai. This happens since i think 2 or 3 months, completely random. It may happen 3 times a day, then it does not happen for a ...
JeFFeR's user avatar
  • 13
0 votes
1 answer
4k views

How to change default engine in WAMP to InnoDB

Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.
Sead Silajdzic's user avatar
0 votes
1 answer
359 views

How to repair mysql tables using myisam storage engine from multiple repairing threads?

I am loading a csv file into a mysql database table that uses MyISAM as storage engine. From the process list I can see that this is accomplished in two phases: 1) reading the csv file and 2) ...
mghh's user avatar
  • 23
1 vote
1 answer
702 views

MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <...
EvyLion's user avatar
  • 11
2 votes
2 answers
466 views

InnoDB errors and crashes with MyISAM database?

I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB ...
st.'s user avatar
  • 123
3 votes
2 answers
7k views

MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY). Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.
Marcus's user avatar
  • 378
1 vote
1 answer
2k views

Will too many tables slow Mysql down?

I'm making a database which should be able to handle over 1,000,000,000 rows. in order to optimize it's performance i've decided to split tables. so instead of having 1,000,000,000 entries in one ...
Pooya Estakhri's user avatar
0 votes
1 answer
666 views

Make Xtrabackup ignore myisam tables & backup only innoDB tables?

I'm using mysql 5.7 (or rather percona 5.7) Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take ...
pedrotester's user avatar
0 votes
1 answer
195 views

Do I need to change the delimiter when creating a procedure using PHP MyAdmin?

I know I need to change the delimiter when manually creating a MySql Procedure but I can't find evidence on how to do it when using the PHP MyAdmin new procedure form or is it even necessary to change ...
Rui Santos's user avatar
4 votes
3 answers
3k views

Estimate query speed for +1.5 billion rows mysql table

I'm working on audio fingerprinting problem where I need to query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to ...
msuliman's user avatar
-1 votes
1 answer
392 views

Table metadata lock MariaDB

I'm having serious problems in a DB, where a "Waiting for table level lock" box is loitering and locks the whole system: Based on some research, when the problem occurred to get the command "SELECT * ...
user avatar
1 vote
1 answer
343 views

Much slower replication on Innodb compared to myisam

Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam. The problem is that when the slave works on innodb, replication ...
dark0612's user avatar

1
2 3 4 5
11