I noticed that 'show processlist' on our MySQL server indicates a lot of threads in 'System lock' state, often followed by just 'Locked', the latter which I'd expect since we have some selects locking behind an update/insert on a MyISAM table. But 'System lock' shows up a lot more than just 'Locked' (sometimes adding up to 2 seconds to ...
I have a myISAM table running in production on mySQL, and by doing a few tests, we've found we can tremendously speed up a query by adding a certain compound index. So far so good. However, I am not really about the best way to add this index in a production environment without locking the table for a long time (it's got 27GBs of data, s...
Can we use constraint like check constraints, not null constraints on MyISAM Storage Engines in MySQL? ...
I heard this today during interview for java developer. I had to list some advantages of MyISAM over InnoDB and why it's still being widely used. And they were waiting to hear from me the answer as the title of this question. As I understand from their own answer: MyISAM doesn't have foreign keys and DB can be easily clustered (one tabl...
I have the following lookup-table: CREATE TABLE `widgetuser` ( `widgetuserid` char(40) NOT NULL, `userid` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`widgetuserid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1; I have a widgetuser_tmp Table with the same structu...
I currently have an application that is using 130 MySQL table all with MyISAM storage engine. Every table has multiple queries every second including select/insert/update/delete queries so the data and the indexes are constantly changing. The problem I am facing is that the hard drive is unable to cope, with waiting times up to 6+ secon...
Hi everyone, have an issue here of how to configure mysql (myisam) properly for the bulk insert (load data infile) to be performed fast. There is 6 Gb text file to be imported, 15 mln rows, 16 columns (some int, some varchar(255), one varchar(40), one char(1) some datetime, one mediumtext). relative my.conf settings: key_buffer = 80...
Hello,I am a web designer from the Netherlands. I am sort of new to building web apps from scratch, i have always used CMS, and customized them. I am building a Auction website in php/mysql, which is finished. Recently i read a few articles about storage engines. I have a few question that have been bothering me for a while now: ...
In phpMyAdmin v2.8.2.4 for MyISAM tables, the "Relation View" appears under the Structure tab. It shows a list of Internal Relations. But what do these do, given that MyISAM does not support foreign key constraints or relational integrity? By phpMyAdmin version 3.2.0.1 this page ("Relation View") no longer appears for MyISAM tables. So ...
Hello, We have a websites with hundreds of visitors every day and tens of thousands queries a day. So, some tables in the database are updated very rarely, some tables are updated few times a minute and some tables are updated ~10 times a seconds. MyISAM uses table-level locking for updates and InnoDb uses row-level locking. So, as I und...
This question is like a continuation of my previous question: http://stackoverflow.com/questions/1722155/am-i-right-that-innodb-is-better-for-frequent-concurrent-updates-and-inserts-than/ But this time I have concrete questions. We know that MyISAM is faster than InnoDb when we don't have many concurrent updates (inserts). When we have ...
A client of mine recently formatted his machine and re-installed MySQL Server and my application. He complained that deleting records from master table is not affecting the child tables. I requested him to send the backup of the database. When I restored the database, I found that the Table Engine has changed to MyISAM whereas they were ...
I am looking to convert my myisam mysql database to an innodb. I seem to keep coming up with conflicting ideas on how to do this or even if I should! What are your thoughts? ...
I've been adding tables to MySQL databases by copying & pasting from a file into the mysql command-line app. My procedure is to copy the CREATE TABLE statement from the file and paste it into my development database and then also paste it into the test database on another machine. I'm the only one using the test database. My CREATE TA...
I did a wipe and restore. I backedup my current innodb tables. (mysqldump) I loaded it into the database. For some reason...the tables are now all myisam instead of innodb...weird! I try to do: ALTER TABLE xxx ENGINE=innodb; And it doesn't do anything to any table. "Show table status" still is "MyISAM" mysql> alter table auth_use...
I'm looking for documentation on how MySQL stores data on disk, in particular InnoDB and MyISAM. I took a database course back in college (and wrote a primitive database) so I have a general idea. I could ask a bunch of questions here about how various types are stored in rows, and how rows are stored on disk, or go whole-hog and just ...
Does a MySQL MyISAM table gets locked when records are deleted from it? ...
I means copying individual MyISAM table files is: (shut down mysqld and copy the .frm, .myd, and .myi files from one database folder to another) Question: (a) can I use this way to backup MySQL database folder from one server to another server with different MySQL version? (b) can this backup files moved to different OS? (example: debian...
Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits? ...
Obviously both have their benefits. MyISAM is fast but may get currupted easily, InnoDB is slow but is more stable thanks to transactions and foreign keys. So it could be good to mix both engines in one database. If that's possible? ...