Hi, I have a table that has 100.000 rows, and soon it will be doubled. The size of the database is currently 5 gb and most of them goes to one particular column, which is a text column for PDF files. We expect to have 20-30 GB or maybe 50 gb database after couple of month and this system will be used frequently. I have couple of questi...
My MysQL database contains multiple MyISAM tables, with each table containing millions of rows. There is a heavy insert load on the database, so I cannot issue SELECTs on that live database. Instead, I create a replica of the database for queries and conduct analysis on that. For the analysis, I need to issue multiple parallel queries....
Please help I am in major trouble with our production database. I had accidentally inserted a key with a very large value into an autoincrement column, and now I can't seem to change this value without a huge rebuild time. ALTER TABLE tracks_copy AUTO_INCREMENT = 661482981 Is super-slow. How can I fix this in production? I can't get ...
I have a MySQL database that contains millions of rows per table and there are 9 tables in total. The database is fully populated, and all I am doing is reads i.e., there are no INSERTs or UPDATEs. Data is stored in MyISAM tables. Given this scenario, which linux file system would work best? Currently, I have xfs. But, I read somewhere ...
We have a large MyISAM table that is used to archive old data. This archiving is performed every month, and except from these occasions data is never written to the table. Is there anyway to "tell" MySQL that this table is read-only, so that MySQL might optimize the performance of reads from this table? I've looked at the MEMORY storage ...
How to specify a trigger which checks if the data inserted into a tables foreign key attribute, actually exists in the references table. If it exist no action should be performed , else the trigger should delete the inserted tuple. Eg: Consider have 2 tables R(A int Primary Key) and S(B int Primary Key , A int For...
Hello, I have a test table. The test table is as follows: CREATE TABLE `mytest` ( `num1` int(10) unsigned NOT NULL, KEY `key1` (`num1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I inserted 50 million rows in this table. When I do show table status the avg_row_length is 7. I was expecting to see 4 since mySQL uses 4 bytes for int...
When you run a delete on a MyISAM table, it leaves a hole in the table until the table is optimized. This affects concurrent inserts. On the default setting, concurrent_inserts only work for tables without holes. However, in the documentation for MyISAM, under the concurrent_insert section it says: Enables concurrent inserts for a...
I want to convert the db with innodb tables into myisam, all of them. How can I do these? there are some foreign keys exist among tables. how can I make this in the best way? ...
Formerly I was using MyISAM storage engine for MySql and I had defined the combination of three fields to be unique. Now I have switched to InnoDB, which I assume caused this problem, and now NULL != NULL. So for the following table: ID (Auto) | Field_A | Field_B | Field_C I can insert (Field_A,Field_B,Field_C) Values(1,2,NULL) ...
I'm planning on doing a lot of INSERT DELAYED into MyISAM tables. But the tables might not exist yet. Let's say e.g. a new table will exist for each day. So instead of detecting absence of table in my client and creating then retrying the insert, it seems like a good case for a stored procedure ("function"). Is this possible, and wha...
I got a TableName.MYD.filepart in MySQL Database ( myisam engine) Anyone can figure out what's that file for? Appreciate! ...
I'm using nested sets to store hierarchical data in a MyISAM table; the table consists of several hierarchical sets for each user. Each user will be the only one writing to his respective trees, but other users may read from them. Node deletion / Insertion requires that other rows in the same tree have their lft and rgt values updated,...
I have a MyISAM table with ~50'000'000 records (tasks for web crawler): CREATE TABLE `tasks2` ( `id` int(11) NOT NULL auto_increment, `url` varchar(760) character set latin1 NOT NULL, `state` varchar(10) collate utf8_bin default NULL, `links_depth` int(11) NOT NULL, `sites_depth` int(11) NOT NULL, `error_text` te...
InnoDB seems almost entirely superior but in what case should I go for MyIsam? Thanks in advance! ...
For Example Let say we have 1000 products in a single category. And we would like to Filter through these products. As we Filter through the products using json. Each time we have need run a separate query to the DB. We were wondering if any one knows if it's possible display a preload the products table. For example preload bar: ini...
I want to migrate one of my database from myisam to innodb. The main reason is to allow my application to honor referential integrity. This is what I did Exported the current myisam database Dropped the database Switched to innodb database http://parasjain.net/2010/06/08/how-to-switch-to-innodb-database-in-mysql/ Verified the engine ...
I have a MyISAM table that basically contains a log. A cluster of machines does single-record INSERTs on this table at a rate of 50 per second tops, but the same table is also SELECTed from by a web application, and indexed to accommodate for this. There are no UPDATEs or DELETEs, though. So from what I've gathered, I should be using co...
I want to create a MyISAM table in MySQL (tried various versions of 5.0.x and 5.1.x) which allows more than 2^32 rows. The way to achieve this is to get MySQL to use 8-byte instead of 4-byte row pointers. Here's a quote from the MySQL manual: If you build MySQL with the --with-big-tables option, the row limitation is increased to 1....
MySQL has special table type MyISAM that does not support transactions. Does Oracle has something like this? I'd like to create write-only database(for logging) that needs to be very fast(will store a lot of data) and doesnt need transactions. ...