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.
508
questions
0
votes
0
answers
16
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).
...
drk1
1
vote
1
answer
37
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:
...
- 476
2
votes
1
answer
21
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)
...
- 41
0
votes
1
answer
64
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 ...
- 145
0
votes
1
answer
50
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 ...
- 21
0
votes
1
answer
258
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)
...
- 183
1
vote
1
answer
35
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 ...
- 37
0
votes
1
answer
30
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)...
- 4,399
1
vote
2
answers
106
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, ...
- 13
0
votes
1
answer
47
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 ...
1
vote
2
answers
1k
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 '...
- 139
0
votes
0
answers
85
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 ...
0
votes
1
answer
239
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 ...
- 103
0
votes
1
answer
385
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 ...
- 1
0
votes
2
answers
205
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 ...
- 389
2
votes
1
answer
9k
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 ...
- 41
0
votes
1
answer
76
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 ...
- 161
0
votes
2
answers
283
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 ...
- 101
2
votes
2
answers
258
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 ...
- 139
0
votes
1
answer
10k
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 ...
- 1
1
vote
1
answer
551
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 ...
- 270
1
vote
4
answers
338
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 ...
1
vote
1
answer
180
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 ...
- 119
1
vote
1
answer
270
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 - ...
- 141
1
vote
2
answers
755
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=...
- 63
1
vote
1
answer
243
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 ...
- 21
0
votes
1
answer
39
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&...
1
vote
3
answers
155
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`...
- 609
0
votes
1
answer
206
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 ...
- 1
0
votes
1
answer
226
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 ...
-1
votes
1
answer
65
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 ...
- 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 ...
- 3
0
votes
2
answers
4k
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 ...
- 137
1
vote
3
answers
362
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 ...
- 11
1
vote
1
answer
222
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. ...
- 61
1
vote
3
answers
7k
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 ...
- 13
0
votes
1
answer
3k
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.
0
votes
1
answer
305
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) ...
- 23
1
vote
1
answer
587
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 <...
- 11
2
votes
2
answers
385
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 ...
- 123
2
votes
2
answers
5k
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.
- 327
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 ...
- 149
0
votes
1
answer
523
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 ...
0
votes
1
answer
132
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 ...
4
votes
3
answers
2k
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 ...
- 81
-1
votes
1
answer
341
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 * ...
Bruno Silva
1
vote
1
answer
304
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 ...
- 11
3
votes
2
answers
2k
views
MariaDB 10.3: InnoDB much slower than MyISAM
I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:
drop ...
- 171
1
vote
0
answers
364
views
MySQL 8 more than doubles the size of MyISAM table after optimization
I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while.
I ...
- 21
2
votes
2
answers
364
views
Table column length is fine in MySQL 5.5 and 5.6, but upgrading to 5.7 results in too many columns
So I have a CMS that is being upgraded from MySQL 5.5 -> 5.7. One table has a 1000+ columns, but it works fine in 5.5 and 5.6. When reaching 5.7, queries involving this table result in a 1117 error. (...
- 121