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
3answers
78 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
76 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
176 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
55 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
45 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
97 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
104 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
304 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
239 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
122 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
43 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
273 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
119 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
94 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
746 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
230 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
63 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
81 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
701 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
51 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
433 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
204 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
74 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
196 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
42 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
2k 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
351 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
38 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
140 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
291 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
156 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
410 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
197 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
182 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
414 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
57 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 ...
0
votes
0answers
153 views

Duplicate error after truncation for Innodb tables ( 5.7.17 - Enterprise edition )

I have seen a strange behavior in MySQL Innodb tables having below structure : CREATE TABLE `bug` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `num` int(11) DEFAULT NULL, `src` varchar(16) NOT NULL, `...
1
vote
0answers
153 views

Auto Change MyISAM to Inno DB

I am beginner in web hosting. I have some site which are created by php/codeigniter.. Today All custom sites (created by codeigniter) are showing these msg Error Number: 1286 Unknown storage engine '...
0
votes
1answer
203 views

InnoDB MySQL table with a trigger that inserts into a MyISAM table

Background I have a Java web application that is used by 300 - 500 users at any given time. On a separate Linux server we have a 5.7.19 MySQL Community Database Server that handles all the ...
0
votes
2answers
375 views

MySQL Storage Engine Changed from InnoDB to MyISAM on restoring backup in lower version

I have two production instance, old production instance have MySQL version "5.1.73-community-log" and the new production instance have MySQL version "5.1.72-community" When I take backup from the old ...
1
vote
1answer
837 views

insert in Mysql innodb table extremely slow [closed]

I can get 2 million inserts per minute on myisam table, but only 3000 insert per minute if I change to innodb CREATE TABLE myisam_table ( c1 int default NULL, c2 varchar(30) default NULL, c3 date ...
1
vote
1answer
673 views

Restoring Individual MyISAM Tables

I'm using Xtrabackup to perform a MySQL DB restore. I found some problems when I'm trying to restore individual tables: tables with InnoDB engine can be restored without any problem but I can't find ...
2
votes
2answers
5k views

mysqltuner - Write Key buffer hit rate

I'm pasting section of MySQL Tuner result regarding MyISAM Metrics. [OK] Currently running supported MySQL version 5.5.57-0ubuntu0.14.04.1-log [OK] Operating on 64-bit architecture -------- ...
1
vote
1answer
1k views

Changing table types from MyISAM to InnoDB in a MySQLdump file

I have a few hundred systems running running on a MariaDB 10.1.x. On these systems there is a master database which, for various legacy reasons, has to have MyISAM tables. I wish to produce slaves for ...
1
vote
2answers
2k views

Changed MyISAM to InnoDB, huge performance loss [closed]

So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss. Here is my.cnf: innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_buffer_pool_instance=2 ...

1 2 3 4 5 10