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
0
votes
0answers
12 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
16 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
40 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
45 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
79 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
173 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
154 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
77 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
39 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
214 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
104 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
74 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 ...
2
votes
2answers
528 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
202 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
54 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
60 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
600 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
2answers
6k 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
45 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
363 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
174 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
58 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
151 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
41 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
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
305 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
36 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
124 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
259 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
149 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
366 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
193 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
164 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
366 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
55 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
128 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
149 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
190 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
327 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
701 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
610 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 ...
2
votes
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
214 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
420 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 ...