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.
510
questions
6
votes
2
answers
2k
views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
0
votes
1
answer
810
views
How to transfer 70GB MySQL with no downtime?
I have a large amount (4000) of individual databases, 90% of which are small. They all include about 20 tables each, and in total, they use about 75GB of space. All tables are MyISAM.
I want to ...
0
votes
1
answer
341
views
MyISAM InnoDB performance
MyISAM vs InnoDB
I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself.
When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver.
...
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),
...
1
vote
2
answers
310
views
Should I create a multi-column UNIQUE index?
I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits):
CREATE TABLE `Visits` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`...
0
votes
1
answer
2k
views
Queries are taking long time to execute
I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine.
Problem started 3 days back, there is long list ...
3
votes
1
answer
3k
views
mysql Wrong bytesec: 0-0-0 at linkstart: 0
I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns:
> +-----------------+-------+----------+----------------------------------- |
> clldata....
6
votes
1
answer
9k
views
mysql table very large size although it doesn't have a lot of records
I have drupal website, one of the tables is cache_form. I have a cron to
DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW());
which works fine.
The size of cache_form.myd is 4.9G ...
1
vote
1
answer
394
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 ...
1
vote
1
answer
422
views
Mysql sequential numbering based on MAX of another column
I've a table lp_pictures which have these fields:
[id | plate_uid]
[1 | aaa]
[1 | aaa]
[1 | aaa]
[1 | bbb]
[1 | bbb]
[1 | bbb]
and i need to UPDATE the field id to becomes:
[id | plate_uid]
[...
2
votes
1
answer
1k
views
Does MySQL guarantee atomic upgrade from read lock to write lock?
If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the ...
3
votes
1
answer
2k
views
MySQL Error 1114 "table is full" Using MyISAM Engine
When executing the following query, we run into the error The table '/var/lib/mysql/temp/#sql_2d22_1' is full (actual table name varies). I've done a fair amount of research on this; most of the ...
8
votes
2
answers
1k
views
Why would MySQL do serial synchronous I/O?
When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
1
vote
1
answer
228
views
Migrating MyISAM tables from 5.5 => 5.6
I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync?
Is this safe?
I know we need to do a full mysqldump/...
9
votes
2
answers
8k
views
Why is MySQL not using the index with the higher cardinality?
I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
1
vote
1
answer
1k
views
MySQL - Best long read query solution within a replication based system
I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in ...
25
votes
3
answers
19k
views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
0
votes
2
answers
94
views
MySQL query optimization for legacy application
I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone.
...
1
vote
1
answer
96
views
Fetching User Recent Status
I am running mysql with MyISAM storage engine to store user status(user updates data).
To load user recent updates data i am using this query:
$limit = 20;
$uid = 3;
SELECT * FROM userstatus WHERE ...
5
votes
1
answer
1k
views
Quick MySQL Backup (1 file per table)
I asked a relevant question few months back and got answer with wide variety of options for backup.
But this time I have different scenario and I have got a database with 7 tables and 1 of them has ...
0
votes
1
answer
1k
views
When and how would MyISAM concurrent insert = always be a problem?
I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table.
We're talking about 10-20 inserts ...
3
votes
1
answer
1k
views
Best way to add a new column to a large table? (MySQL - MyISAM)
Here's more info on the table that needs to be altered:
15GB size MyISAM table with Index around 5GB
Over 100 million rows
MySQL 5.1 on RHEL 5 with 4GB RAM
Currently live online
What's the best way ...
1
vote
1
answer
2k
views
InnoDB to MyISAM Conversion Problem
I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin
I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value
...
3
votes
2
answers
4k
views
Why does MySQL fail to use an index effectively when LIMIT offset is large?
I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec:
SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5;
But querying ...
6
votes
1
answer
1k
views
MyISAM with Innodb in the same Wordpress database reduce performace?
My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine.
I use MySQL 5.5.35-...
1
vote
2
answers
4k
views
MySQL views and table locking (5.1 MyISAM)
I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM.
If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?
13
votes
2
answers
15k
views
How to drop empty tables
Is it possible to drop all empty tables from my huge database (mysql)?
I'm looking for a sql command to automatically remove all those empty tables.
Currently, I have 305 tables in my dataset, and ...
0
votes
2
answers
628
views
Should we explicitly lock MyISAM tables or is it implicit....?
Does MyISAM tables automatically lock a table for each select or insert statement ....
I mean if I query the database as select * from tablename then does the MySQL engine first lock it for read ...
0
votes
2
answers
612
views
Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.
I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
4
votes
3
answers
2k
views
MySQL Tables Require Daily Repairs - Server, Table or HD?
I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.
The bigger ones ...
1
vote
2
answers
787
views
Why does MySQL + Windows Server = crashed tables periodically?
Windows Server 2008 R2 x64
MySQL Server 5.6.14 x64
Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by ...
3
votes
1
answer
2k
views
mysql: find column data size on disk
I have fairly large MyISAM table with longtext field in it
Table size is somewhat 16GB.
I need to know if clearing this column in old records helps to keep table small
I can retrieve table wise ...
2
votes
1
answer
333
views
How safe is it to widen a column in MySQL?
Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot.
A MySQL database I volunteered to help maintain has a table that looks like ...
1
vote
1
answer
760
views
MySQL MyISAM index causes query to match no rows; indexes disabled, rows match
I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found.
Commands ran:
CREATE ...
0
votes
1
answer
3k
views
My database keeps crashing on startup how can i repair it without it running?
I've got some problems with my database. I hope somebody can help me out.
Problem:
The database keeps crashing on startup. In my log i see a couple off these errors:
[ERROR] /usr/libexec/mysqld: Table ...
0
votes
1
answer
210
views
Few Insert queries rollbacked due to system crash
Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked.
I am sure that they were proper ...
0
votes
1
answer
204
views
is it possible to set engine for database (not the tables)?
There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server.
They were imported and were displayed in the tree structure of phpmyadmin (under the ...
1
vote
1
answer
588
views
Need to optimize simple SELECT query for InnoDB table with 2million rows
I'd like to make this query the fastest possible, since it's called really often.
SELECT login_events.id
FROM login_events
WHERE (
DATE(created_at) >= DATE(CURRENT_DATE)
AND person_id = 1
) ...
0
votes
2
answers
293
views
MySQL using swap during conversion to InnoDB
I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions ...
2
votes
2
answers
6k
views
Setting default CHARSET and DB Engine
I recently updated my server's database system from MySQL to MariaDB.
After update, everything works well.
But, when I went to phpMyAdmin, the default charset and types are changed:
At the end of ...
1
vote
1
answer
3k
views
Mysql Performance tuning for myisam tables.
Folks
I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records ...
1
vote
1
answer
986
views
Backing up MySQL w/MyISAM on Amazon RDS
So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS. I've just taken over this application, and I'm concerned we're not getting good backups, since ...
0
votes
1
answer
2k
views
MySQL Read Speed and Partitioning on Separate Drives
Introduction
Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
2
votes
1
answer
2k
views
Is it safe to kill ANALYZE TABLE query on a MyISAM table?
I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an ...
2
votes
1
answer
2k
views
Issue moving database between servers (cannot find .frm file even though it's there)
I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great.
The name of my database is roster and it has one table called users. ...
3
votes
1
answer
251
views
What if I want to keep using MyISAM
In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
4
votes
2
answers
4k
views
Repair MySQL table without downtime
We have a corrupt MyISAM table in our production MySQL database. Is there a method for repairing this table without locking it?
We were considering using a method similar to Facebook's Online Schema ...
2
votes
1
answer
627
views
MyISAM vs InnoDB for Forums [closed]
I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
3
votes
2
answers
2k
views
How to backup a MySQL database without slowing the application?
I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
2
votes
1
answer
140
views
How to go about modeling the main object of relationship?
I'm using MySQL and MyISAM tables.
I have a bunch of objects that I want to group together
1 object belongs to 1 and only 1 group
1 group can have many objects
1 group has 1 required main object ...