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
1
vote
2answers
235 views

MySQL CREATE INDEX slowing down

My analytics table has 190M rows, about 150GB ins size. Storage engine is MyISAM with key-buffer-size=16G myisam_sort_buffer_size=2G myisam-max-sort-file-size=200G. Machine has 32GB memory. While ...
1
vote
1answer
544 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.
6
votes
2answers
5k views

Index before or after bulk load using load infile?

I have a database with over 1B rows and two columns that are indexed (in addition to the PK). Is it better to have the index pre-defined in the table before the load infile or better to index after ...
0
votes
1answer
183 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 ...
0
votes
1answer
93 views

MYSQL Multiple table optimization for advanced search

I have a users table where there is the basic information of each user: CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `account_type` varchar(10) NOT NULL, `email` varchar(...
0
votes
1answer
126 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 ...
30
votes
4answers
46k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
0
votes
2answers
908 views

Mysql SHOW OPEN TABLES shows In_use but nothing in SHOW PROCESSLIST

I'm looking for advice for a MySQL database used for my development. I have a table is locked, although no process is active. Here's some context: I am a developer on this MySQL development database ...
2
votes
2answers
56 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 ...
1
vote
1answer
145 views

Finding temporary table name in MariaDB

As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files. Our ...
0
votes
2answers
716 views

Repair by Sort not working

I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql ...
0
votes
1answer
216 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 <...
1
vote
1answer
174 views

C API hangs in mysql_query()

I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours only for this table: CREATE TABLE `ReferenceWaves` ( `ReferenceWave` datetime NOT NULL COMMENT 'DateTime of ...
2
votes
1answer
294 views

Proper Way to Remove Mysql Tables

I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?. Removing the .frm files ...
0
votes
1answer
222 views

MySQL MyIsam Compressing tables

Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows) Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
0
votes
1answer
187 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 -...
4
votes
1answer
6k views

How can I find out the last time a MySQL table was analyzed?

I'm interested in this for InnoDB mostly, but also for MyISAM. I looked around in information_schema and did not see this data anywhere. I'm using MySQL 5.5.16.
1
vote
1answer
161 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 ...
5
votes
1answer
648 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
448 views

Safe method to rename a crashed myisam table

I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, ...
1
vote
4answers
46 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
1answer
46 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 ...
0
votes
2answers
4k 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 ...
0
votes
1answer
667 views

MySQL MyISAM REPAIR TABLE lost connection to MySQL server during query

So I've been building up over time a database, and unfortunately I was using MyISAM which didn't have any problems until the database became huge. After massive CPU spikes, I created a script to ...
0
votes
0answers
22 views

Do MyISAM tables crash from a “clean” mysqld restart?

I've researched this topic a little bit and available information indicates that MyISAM tables should not crash on their own from a simple server restart. Usually the following causes are put forth: ...
3
votes
1answer
1k views

MySQL: error 1118 (42000) row size too large

storage_engine = MyISAM table_type = MyISAM version = 5.1.25-rc database_name.sql dump size is 3.3G: mysqldump -u user -p database_name > database_name.sql When I try to restore the database (...
1
vote
2answers
2k views

Can I emulate Oracle's autonomous transaction in MySQL?

I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any ...
0
votes
2answers
96 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), ...
1
vote
1answer
86 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 ...
0
votes
1answer
47 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 - ...
2
votes
2answers
108 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. (...
1
vote
2answers
42 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=...
0
votes
1answer
91 views

Convert table from MyISAM to InnoDB and keep MyISAM rows order

I'm currently rewriting old project and I've got this strange scenario. It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part ...
1
vote
1answer
38 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 ...
0
votes
1answer
30 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
3answers
56 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`...
0
votes
1answer
43 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 ...
0
votes
1answer
35 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 ...
0
votes
2answers
632 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 ...
7
votes
3answers
696 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=...
6
votes
2answers
16k views

Add UNIQUE index fails with duplicate entry error, but no duplicates found

Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT ...
-1
votes
1answer
31 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 ...
0
votes
1answer
46 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 ...
4
votes
2answers
1k views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
2
votes
2answers
12k 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 ...
1
vote
3answers
117 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 ...
8
votes
3answers
8k views

Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "...
3
votes
2answers
1k 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 ...
1
vote
1answer
89 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. ...
1
vote
1answer
524 views

Extremely slow MyISAM slave updates

I have 2 slaves where one is working as expected, but the other slave is extremely slow when executing update statements. The insert and delete statements are fast. I used the command check table ...

1
2 3 4 5
10