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
39 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=...
1
vote
1answer
33 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
53 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
32 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
28 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
1answer
26 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
45 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 ...
0
votes
2answers
183 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 ...
1
vote
3answers
99 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 ...
1
vote
1answer
85 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. ...
0
votes
2answers
682 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 ...
0
votes
1answer
260 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
0answers
21 views

Explain plan : Why rows examined higher in this cased? [duplicate]

I have 450 million records in my table which contains the data for around 7 to 8 months. So per month data is around 60 million records. Table has index on empDate. When I execute below query , I ...
0
votes
2answers
59 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) ...
0
votes
1answer
142 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 <...
2
votes
2answers
125 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 ...
1
vote
2answers
558 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.
0
votes
1answer
439 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 ...
0
votes
1answer
161 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
1answer
46 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 ...
1
vote
3answers
448 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 ...
-1
votes
1answer
131 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 * ...
1
vote
1answer
126 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 ...
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
0answers
257 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 ...
2
votes
2answers
85 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
1answer
113 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 ...
2
votes
0answers
837 views

Database design for Subscription, Coupons and Plans

I have searched a lot in SO and SE but I did not get anything particularly about this kind of database design with coupon codes included. Please take a look at this problem. I am developing a website ...
6
votes
3answers
7k 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 "...
1
vote
1answer
59 views

What may cause MyISAM index corruption?

I've had an error message telling about index corruption on the .myi file Reparing the table fixed the issue This table is accessed with select only, so what is likely to corrupt indexes ? CREATE ...
1
vote
3answers
599 views

Event doesn't work

I have a working command: UPDATE ads SET ad_points = ad_points - 20 And I would like to perform this action every day. After creating the event in phpMyAdmin (MYSQL) nothing happens (on the next ...
0
votes
1answer
243 views

Zero Date Values & Converting Entire DB From MyISAM to InnoDB

I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's ...
0
votes
1answer
84 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(...
-1
votes
1answer
259 views

Website down with 3,000 concurrent user 4Gb Memory Dedicated Server (myIsam - MySQL) [closed]

Our site has 3,000+ concurrent users when we open our registration on our website. The site was too slow and get the server down sometime, even sub accounts websites are down. Free memory was 400MB. ...
-1
votes
1answer
45 views

Can I tweak Mysql [buffer?] variables to speed up indexes building?

I have disabled indexes updates with alter table my_table disable keys to speed up inserts Are there variables I can set [for the session] to speed up indexes rebuilding with alter table my_table ...
4
votes
2answers
3k views

“Create Unique Index” on big table taking too long time [closed]

I am trying to restore GHTorrent's database dumps (CSV files containing GitHub metadata). The commits table has more than 891 Million rows and the project_commits has more than 5.4 Billion rows. Since ...
0
votes
1answer
28 views

Mysql - Rows not updated in transactions without any errors

I have a stored procedure that updates a user's balance and inserts a transaction log. I don't know why, but sometimes it doesn't update the users table. Stored Procedure Code DELIMITER $$ USE `...
-1
votes
1answer
378 views

restoring MySQL MyISAM table from files

0 down vote favorite We have damaged disks on the server and partially lost data. Backup of the database was not the most relevant, so it was decided to restore the database from the .myd, .myi, .frm ...
0
votes
1answer
40 views

Optimitze Table, MyIsam

I was reading a post on Percona (https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/) that said dropping and adding an index on a innodb table was almost 20* faster than using ...
2
votes
1answer
197 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
309 views

mysql_query() is NOT passing the command to the MySQL server using the C API

I have this question asked but after a deeper investigation I realized that hanging is not the issue. After reading this thread I decided to ask a new question. For example I pass this string to the ...
1
vote
1answer
167 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 ...
1
vote
3answers
491 views

Optimize MAX or ORDER BY column DESC with LIMIT 1

I have a scenario where I need to Insert a new record and increase a sub-id. The Query is very slow. Exist any better Solutions for this? Table CREATE TABLE `link_` ( `id` bigint(20) NOT NULL ...
2
votes
2answers
207 views

Terribly slow Disk Read (I/O) When fixing corrupt MyISAM table (9GB)

So I've launched the myisamchk utility with repair option on all tables within the database. Smaller tables (1GB max) got fixed in no time, virtually seconds. However, my largest table (9GB) takes ...
2
votes
1answer
203 views

Why is a BIGINT index larger than a CHAR 13 index?

I have a MySQL DB, with a MyISAM table with 5 million records, and several fields. 1 of the fields is a BIG INT, which I understand to be 8 bytes, and another is a CHAR 13, which I thought was a byte ...
1
vote
1answer
463 views

How can I trace the root cause of a MySQL database table that crashed?

Are there any standard practices or methods for tracing the cause of a MySQL table crash? If I repair a table that had crashed, how do I determine what caused it to crash in the first place? Storage ...
0
votes
1answer
60 views

When the number of users increases, the site is slowed down

I have a database with 25 M record in 42 tables(all tables is Myisam). When the number of users increases, the site is slowed down . pleas help me . Server Config: Linux , Centos Ram : 30 GB CPU : 20 ...
1
vote
2answers
3k views

ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option

I am new to MySQL administration, and this week a customer came to me with a problem. They created an application with Wamp in a Windows Server environment. For some reason when listing the tables in ...
7
votes
3answers
3k views

Occasionally simple queries are slow with InnoDB caused by slow query end [closed]

We are having serious issues with one of our productions servers. The setup in question is CentOS 7 64-bit server with 16 GB of ram and 250GB SSD (so the setup should be very fast). The LEMP stack is ...

1
2 3 4 5
10