Here at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB… a foreign key, and it has the exact column definition in the same order. In other words, it checks for duplicate keys, but… a foreign key. This is the output of our tool when run on the same table, correctly identifying the redundancy: $ pt-duplicate-key…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… allocators on MySQL performance can be found in this blogpost. This release of Percona XtraDB Cluster has fixed number of foreign key and packaging…). Cascading foreign key constraint could lead to unresolved replication conflict and leave a slave hanging. Bug fixed #1130888 (Seppo Jaakola). If MySQL replication…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… on MySQL performance can be found in this blogpost. (Ignacio Nin) This release of Percona Server for MySQL has fixed a number of performance… with –innodb-optimize-keys option it produced invalid SQL for cases when there was an explicitly named foreign key constraint which implied an…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… on MySQL performance can be found in this blogpost. (Ignacio Nin) This release of Percona Server for MySQL has fixed a number of performance… with –innodb-optimize-keys option it produced invalid SQL for cases when there was an explicitly named foreign key constraint which implied an…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 …as well as making foreign keys performed row by row which often can be very inefficient. As results of Foreign Key limitations you might …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… learn that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0 to disable enforcement… about both primary keys and foreign keys. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
… much of a tradeoff you are willing to make for performance. In some situations the cost can be considerable”. .. that’s… NOT NULL, bogus_column char(32), PRIMARY KEY (id), KEY (parent_id), CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ) ENGINE… without the foreign key constraints does not block on the last statement. We also see the expected output change to: mysql> SELECT…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL ….commanual foreign_key_checksblogpercona….Performance_schema_mutex_classes_lostblogpercona.commanual Performance_schema_mutex_instances_lostblogpercona.commanual Performance_schema_rwlock_classes_lostblogpercona.commanual Performance_schema_rwlock_instances_lostblogpercona.commanual Performance…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… you ORM by joining all tables connected with foreign keys just in case) which would take 5 …yet making it unusable to check the optimizer performance. Solution for this problem was to use …found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB… primary key, fkto3 char(32) not null, fkfrom3 char(32) not null, index(fkto3), index(fkfrom3), foreign key (fkto3) references dos3(fkfrom1), foreign key (fkto2… at all, and that we want to achieve the best performance possible, so we’re going to convert everything to ASCII…
