Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
32 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
0answers
30 views

recovering mysql database after having deleted myisam table files [closed]

I did a test on a large myisam table updating millions of rows. This took too long, so I cancelled the query. After that, my sql client (HeidiSQL) became unreactive. So I stopped the mysql service and ...
0
votes
1answer
17 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
13 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
155 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 ...
0
votes
1answer
23 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
40 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 ...
1
vote
1answer
128 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
76 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 ...
1
vote
1answer
29 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
37 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
316 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 ...
5
votes
2answers
4k 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
34 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
227 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
109 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
47 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
116 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
38 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 ...
3
votes
2answers
1k 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
202 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
33 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
80 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
199 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 ...
3
votes
1answer
127 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
280 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 ...
3
votes
2answers
136 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
137 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
275 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
48 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
2k 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
2k 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
92 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
135 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
141 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
254 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
502 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
438 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
4k 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
861 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
1k views

Changed MyISAM to InnoDB, huge performance loss

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 ...
0
votes
2answers
155 views

MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time

I want to create a pair of table with a structure along the lines of: CREATE TABLE tableA ( ext_id bigint NOT NULL, local_id int AUTO_INCREMENT NOT NULL, /* <-- Requires MyISAM (InnoDB ...
3
votes
1answer
342 views

Is there any disadvantages if i shift from myisam to innodb in mysql 5.7?

We are using myisam primarily for full text on mysql 5.5 ,However as 5.6+ innodb supports full text i want to know any performance or other bottleneck ,Please suggest ,we have huge data set in ...
0
votes
2answers
2k views

Garbage collection in database?

its kind weird idea which I would need suggestions. A classical example: users ----- ID | USERNAME 1 test1 2 test2 3 test3 emails ------ USER_ID | EMAIL 1 test1@email.com 2 ...
0
votes
1answer
475 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 ...
2
votes
1answer
841 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 (...
0
votes
1answer
74 views

Extract All Links table

I have a table just like attached image. The query i am using to fetch the data is SELECT message_text FROM messages WHERE conversation_id = "69_P" AND message_type = "text" AND ...
2
votes
2answers
783 views

Migrating MySQL from Windows to Linux

I'm new to MySQL. However I am very familiar with Oracle and MSSQL. This is what I have done so far: Installed MySQL 5.7 on RHEL 7.2 Created a test db called "mydb" and test table called "employee"....
2
votes
2answers
91 views

Different MySQL index sizes after restore

I've been trying to create a backup for a very large MyISAM DB with mysqldump and I've noticed that some of the tables have variations in index sizes after restoring. For example, one of my larger ...