Can I convert Innodb with foreign directly into mysiam?
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? ...
innodb
MySQL Insert Query Randomly Takes a Long Time
I am using MySQL to manage session data for my PHP application. When testing the app, it is usually very quick and responsive. However, seemingly randomly the response will stall before finally completing after a few seconds. I have narrowed the problem down to the session write query which looks something like this: INSERT INTO Session...
MySql, InnoDB & Null Values
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) ...
Unique constraint with nullable column
I have a table that holds nested categories. I want to avoid duplicate names on same-level items (i.e., categories with same parent). I've come with this: CREATE TABLE `category` ( `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(100) NOT NULL, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY...
Reduce durability in MySQL for performance
My site occasionally has fairly predictable bursts of traffic that increase the throughput by 100 times more than normal. For example, we are going to be featured on a television show, and I expect in the hour after the show, I'll get more than 100 times more traffic than normal. My understanding is that MySQL (InnoDB) generally keeps m...
Find node level in a tree
I have a tree (nested categories) stored as follows: CREATE TABLE `category` ( `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(100) NOT NULL, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`category_id`), UNIQUE KEY `category_name_UNIQUE` (`category_name`,`parent_id`), KEY `fk_categor...
Mysql InnoDB performance optimization and indexing
Hello everybody, I have 2 databases and I need to link information between two big tables (more than 3M entries each, continuously growing). The 1st database has a table 'pages' that stores various information about web pages, and includes the URL of each one. The column 'URL' is a varchar(512) and has no index. The 2nd database has a ...
Zend Framework - Problem with Database Table Recursive Cascading Deletes
My situation may be a bit abnormal, but I have foreign keys defined in my MySQL database, while enforcing referential integrity in the Zend_Db_Table classes. The tables use the InnoDB storage engine. When deleting a record, the Zend Framework will properly identify immediate children via the $_referenceMap in the table model and delet...
MySQL InnoDB Cascade Rule that looks at 2 columns?
I have the following MySQL InnoDB tables... TABLE foldersA ( ID title ) TABLE foldersB ( ID title ) TABLE records ( ID folderID folderType title ) folderID in table "records" can point to ID in either "foldersA" or "foldersB" depending on the value of folderType. (0 or 1). I am wondering: Is there a way to create a CASCAD...
Always-indexed MySQL indexing/searching replacements for InnoDB?
I am using InnoDB for a MySQL table, and obviously queries using LIKE and RLIKE/REGEXP can take a lot of time. I've tried Spinx, and it works great, except I have to re-index context at intervals. I can re-index every minute, but I am wondering if there is either 1) a setting in Sphinx to keep records always indexed or 2) other software...
SQL LEFT JOIN help
My scenario: There are 3 tables for storing tv show information; season, episode and episode_translation. My data: There are 3 seasons, with 3 episodes each one, but there is only translation for one episode. My objetive: I want to get a list of all the seasons and episodes for a show. If there is a translation available in a specified...
Create MySQL database with default InnoDB tables?
I've been working on writing a SQL statement to create a MySQL database with several default options, including default character set and default collate. Is it possible to add syntax to make the default engine type for tables in this database to be InnoDB? I've been looking through the MySQL manual for v.5.1 and I've found the statem...
NHibernate, MySQL, InnoDB and nested transactions
I'm using NHibernate to access an MySQL database using InnoDB tables. I know InnoDB doesn't support nested transactions, but I keep thinking things would have been much simpler if it did. Take a look at this method for instance.. SessionManager opens a new session per thread. If I didn't store the transaction, it previous would have bee...
Do table locks scale? / Would row locks be more efficient for nested sets?
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,...
why is django leaving locks behind in mysql?
I have a Django app that uses MySQL and the InnoDB engine for storage. For some reason, Django sometimes leaves locks in place, even after the query has completed. (I can see them with Innotop). The only transaction-handling stuff that I do in my code is that I have django.db.transaction.commit_on_success specified for some of my save...
Increment value on new row
Given this table design in a web application: CREATE TABLE `invoice` ( `invoice_nr` int(10) unsigned NOT NULL AUTO_INCREMENT, `revision` int(10) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`invoice_nr`,`revision`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci What's the most handy and reliable way to insert a new ...
What does the query 'show innodb status' in MySQL mean?
What does the query show innodb status return? ...
MySQL Transaction handling with Insert Ignore (or similar) and Unique IDs
I'm using MySQL + PHP. I have some code that generates payments from an automatic payment table based on when they are due (so you can plan future payments... etc). The automatic script is run after activity on the site and sometimes gets run twice at the same time. To avoid this, we generate a uuid for a payment where there only can be ...
Why is my SQL UPDATE statement not working in a loop?
The update statement in example is not working all the time even though the where clause is true. The database is MYSQL innodb. Would that cause some sort of locking ?? This is so weird. <?php $query = 'SELECT id FROM TABLE1'; $result = db_query($query); while($row = db_fetch_array($result)) { //do some process...
Would any configuration or INNODB principle cause the following MYSQL update to not necessarilly perform all updates?
<?php $query = 'SELECT id FROM transaction_table'; $result = db_query($query); while($row = db_fetch_array($result)) { //do some processing db_query('UPDATE transaction_table SET updated = "1" WHERE id = "%s"',$row['id']); } ?> Every time this script run, it only updates a few random rows (8-25 on ...