I think you’re misunderstanding what ON DELETE NO ACTION means. It does not mean to suppress the foreign-key constraint. When you delete a record that is referred to by a foreign key, InnoDB has the ability to take an automatic action to rectify the situation: it can CASCADE, meaning, delete the referring record. (This would … Read more
MyISAM effectively works in auto-commit mode (as it’s not a transactional engine), and it just ignores the commit/rollback. Actually storage engine is a different layer in the MySQL architecture, separated from the SQL parser, the SQL layer communicates to the storage engine with lower-level API, and that’s the reason there is a common SQL and … Read more
What jumps out immediately at me is MyISAM. ASPECT #1 : The JOIN itself Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level lock behavior instead of row-level locking because of MyISAM’s involvement in the query and MVCC cannot be applied to the MyISAM data. MVCC cannot even be … Read more
Kindly tell me, In this situation what engine I have to use for improve performance? The performance of each storage engine will depend on the queries you perform. However, be aware that different tables within the same database can use different storage engines. Why MyISAM engine does not support foreign key relationship and InnoDB does? … Read more
The main difference is that InnoDB supports transactions while MyISAM does not. There are numerous other differences, however the common one’s i am aware of are: MyISAM has typically been considered faster at searching, but recent InnoDB improvements are removing this difference and improving high concurrency workload performance InnoDB support transactions whilst MyISAM does not … Read more
Replace TYPE=MyISAM with ENGINE=MyISAM The problem was “TYPE=MyISAM” which should be “ENGINE=MyISAM” as per MySQL version updates – a simple search / replace has fix it.
From the manual: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html For example, to convert a table to be an InnoDB table, use this statement: ALTER TABLE t1 ENGINE = InnoDB; The outcome of attempting to change a table’s storage engine is affected by whether the desired storage engine is available and the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in … Read more
If you use SHOW CREATE TABLE, you have to parse the engine out of the query. Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely). The correct query to use is SHOW TABLE STATUS – you can get information … Read more
What about Transactions? They have the ROLLBACK-Feature. @see https://dev.mysql.com/doc/refman/5.0/en/commit.html For example: START TRANSACTION; SELECT * FROM nicetable WHERE somthing=1; UPDATE nicetable SET nicefield=’VALUE’ WHERE somthing=1; SELECT * FROM nicetable WHERE somthing=1; #check COMMIT; # or if you want to reset changes ROLLBACK; SELECT * FROM nicetable WHERE somthing=1; #should be the old value Answer on … Read more
InnoDB and MYISAM, are storage engines for MySQL. These two differ on their locking implementation: InnoDB locks the particular row in the table, and MyISAM locks the entire MySQL table. You can specify the type by giving MYISAM OR InnoDB while creating a table in DB.