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.
58
questions with no upvoted or accepted answers
4
votes
0
answers
1k
views
Dynamic MySQL partitioning based on Unix Time
My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing Unix time.
I am designing an ...
- 173
3
votes
1
answer
2k
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 (...
- 31
2
votes
1
answer
414
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
0
answers
2k
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 ...
- 121
2
votes
0
answers
53
views
Tuning Issue / Temporary Tables
I've got a MySQL database (MyISAM) that handles a lot of read/writes and uses temporary tables quite frequently.
Recently it's started having massive load spikes at peak load, and noticed that there ...
- 113
2
votes
0
answers
995
views
"Unexpected EOF" during SELECT on huge MyISAM table
When executing a SELECT query that uses filesort on a huge table (2 billion rows) the execution stops after ~30mins with the following error message:
ERROR 9 (HY000) at line 1: Unexpected EOF found ...
- 223
2
votes
0
answers
2k
views
How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?
I have created table as
post (
post_id int(15) auto_increment primary key ,
user_id int(11) ,
post_text varchar(2000) ,
post_type int(1),
...
- 33
2
votes
0
answers
3k
views
MySQL - high CPU usage (MyISAM tables)
We have a server running LAMP stack on CentOS release 5.6 (Final)
MySQL is having high CPU usage constantly which causes server to lag as shown
top - 13:50:47 up 80 days, 13:53, 1 user, load ...
- 21
2
votes
0
answers
176
views
MySQL Partitioning Performance
I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM.
I am unable to index ...
- 157
1
vote
0
answers
32
views
Wrong query optimization plan with InnoDB
After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage.
The problem are some queries that are wrong optimized with the new engine.
...
- 11
1
vote
1
answer
12k
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 ...
- 11
1
vote
4
answers
376
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
1
answer
630
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 <...
- 11
1
vote
1
answer
319
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 ...
- 11
1
vote
0
answers
389
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 ...
- 21
1
vote
0
answers
177
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 '...
1
vote
0
answers
52
views
MySQL lock management
Query Q1 is executed which selects from table a.
a is locked for writing (L1) while the Q1 runs.
When query Q2 attempts to write to table a, table a is immediately locked for reading (L2). Q2 is in ...
- 121
1
vote
0
answers
55
views
Can MyISAM tables affect the backup recovery of innodb tables on Amazon RDS?
I have an Amazon RDS instance containing several databases. All the tables are innnodb except from one, that is MyISAM and currently cannot be converted to innodb.
I know that the recovery of MyISAM ...
- 111
1
vote
0
answers
437
views
Cardinality of an index in a MySQL table is equal to table's row size
I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct ...
- 3,029
1
vote
0
answers
756
views
Mysql slow copying to tmp table to indexing
I try to indexing table.
The table size is about 60GB.
But indexing query is hang on 'copying to tmp table' 18 hours.
I found advice setting 'myisam_sort_buffer_size'
myisam_sort_buffer_size : 128M
...
KIM
1
vote
0
answers
104
views
Tuning MySQL MyISAM for large joined updates
I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
- 131
1
vote
0
answers
69
views
Migrated servers... Innodb tables stayed innodb, but database is showns as MyISAM. Problem?
I moved servers. I exported DB on old, imported to new. Kept getting syntax error on import until I tried no compression. Weird, but it worked. I was skeptical so I compared the two carefully and ...
- 111
1
vote
0
answers
1k
views
Suddenly high CPU usage
I've read a lot about MySQL optimisation and I already optimised MySQL about a year ago. Reason for that was a high system load average. I tuned many queries and edited the my.cnf file. Since then ...
- 11
1
vote
0
answers
150
views
Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table
I have a very large table (15 billion rows) with a structure like this:
CREATE TABLE `textlines` (
`PGUID` binary(16) DEFAULT NULL,
`StatID` int(11) DEFAULT NULL,
`Time` datetime(3) DEFAULT ...
- 11
1
vote
0
answers
883
views
Question on MySQL insert race condition
I'm using MySQL 5.1, PHP 5.3 and the table is MyISAM. Here's the DTD of this very fake table:
CREATE TABLE `temp1` (
`value` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This ...
- 111
1
vote
2
answers
5k
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 ...
- 213
1
vote
0
answers
690
views
Missing rows in MyISAM tables with 10-20 billion records
I've got 2 tables with 10-20 billion records each.
Currently these are in MyISAM (MySQL).
They only require to be built and indexed once, after this only SELECT is used.
They are currently ...
- 157
1
vote
0
answers
312
views
How can I force myisamchk to modify the original data file in case of duplicate keys?
In the documentation for myisamchk, it says:
--quick, -q
Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to modify ...
- 321
1
vote
0
answers
207
views
How to speed up this query
MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
- 1,345
1
vote
0
answers
119
views
Why do we need myisam spatial extension anyway?
If just to determine whether a point is within a rectangle,
What is wrong with this query?
EXPLAIN SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude
FROM
TableBusiness AS TB
WHERE
-6....
- 1,345
1
vote
1
answer
393
views
Concurrency Issues when multiple users uses Portal (MySQL and PHP)
We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no ...
- 11
0
votes
0
answers
21
views
MyISAM table repair stuck in loop
my MyISAM table is broken (I know it's better to use Innodb), but now I need to fix this large table. 200GB
I have already tried the following options, but the table repair seems endless and the TMD ...
- 21
0
votes
1
answer
24
views
Mysql MYISAM force operating on a crashed database
Is theres a way to forcibly operate on a database that crashed (even if it's not possible through mysql cli at least a way to remove the dirty bit that marks it as crashed by editing with an editor).
...
drk1
0
votes
1
answer
138
views
MySQL update with inner join taking too long
I have the following two tables
CREATE TABLE `tbl_products_temp` (
`cd_id` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`cd_structure` MEDIUMBLOB NOT NULL,
`cd_smiles` TEXT NULL ...
- 155
0
votes
1
answer
42
views
wrong index in composite PRIMARY KEY in MyISAM
I use MyISAM for processing tables, as it is much faster to read large data from MyISAM as compared with InnoDB for JOINs. I create map tables for bulk INSERT,
CREATE TABLE tag_map
(
article_id int(11)...
- 4,479
0
votes
0
answers
107
views
MySQL: Migrate MyISAM table with multicolumn primary index with autoincrement to InnoDB
I have a large (10M rows) MyISAM table with a primary index on 2 different columns where one of them is auto_increment.
How can I migrate it to InnoDB in MySQL 5.7?
The table is something like:
CREATE ...
0
votes
2
answers
366
views
in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?
I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload.
Based on this What are the main differences between InnoDB and MyISAM?
When ...
- 101
0
votes
1
answer
40
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&...
0
votes
1
answer
284
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
1
answer
3k
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
1
answer
156
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 ...
0
votes
1
answer
266
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
0
votes
1
answer
65
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 `...
- 101
0
votes
1
answer
835
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 ...
- 113
0
votes
1
answer
667
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, ...
- 339
0
votes
1
answer
349
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
1
answer
1k
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
0
answers
890
views
MYISAM can't create table: Row size too large
We are having database in MySQL 5.1, we have taken the DB backup from MySQL 5.1 and trying to restore in MySQL 5.6. We are getting below error:
ERROR 1118 (42000) at line 603: Row size too large. ...
- 1,007
0
votes
0
answers
926
views
CREATE OLAP server
I have a MySQL database server along with replication server, i.e. OLTP system altogether.
As I have very heavy data fetching queries for the decision making reports. I need to create data-ware house ...
- 199
0
votes
1
answer
135
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 ...
- 171