…key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql…mysql> SHOW…keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… ha_innodb.cc in 5.1: static MYSQL_SYSVAR_ULONG(thread_concurrency, srv_thread_… (lots of threads waiting for semaphores in SHOW INNODB STATUS), you can try to…foreign key constraints)? Any concrete examples are appreciated. HT: Yes, it is sufficient that a foreign key and the referenced key…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
… loading the entire 4GB file in one chunk: time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique… other terminal: while [ -e /tmp/my-fifo ]; do time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique…-Tree indexes. The two curves on the Cacti graph actually show both files being loaded. It might seem counter-intuitive, but…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… primary key, empname varchar(20), deptid integer, key fk_deptid (deptid), constraint fk_deptid foreign key(deptid) references dept(deptid) ); * Find below output which shows indexes in the database. (Query for below output is at the end) mysql…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…in reads, as InnoDB is transactional, supports Foreign Key and has an operational overhead. As …MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql…primary key. InnoDB is faster than MyISAM by 6-9%. Falcon shows very bad scalabilty. READ_KEY…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
… to both MyISAM and InnoDB/XtraDB. Tests show that PBXT’s performance is similar to InnoDB…0.09) supports the MySQL Backup API which was originally implemented in MySQL 6.0. This feature … engine. PBXT supports row-level locking and foreign keys. Does this create any additional locking overhead …
Post: Converting Character Sets
March 17, 2009 By 22 Comments
…(0.02 sec) Records: 0Â Duplicates: 0Â Warnings: 0 mysql> SHOW CREATE TABLE `t1`\G *************************** 1. row *************************** Table: t1 … – Proper handling of string foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys anyway …) – Allow …
Comment: How to calculate a good InnoDB log file size
June 1, 2010 By 38 Comments
…, deleted the data+log files, but when I start mysql again the log shows: 100601 09:08:30 mysqld started InnoDB: The…: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 100601 9:08:30…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
…mysql error log, which looks something like this: =========================================== 090420 12:09:32 INNODB TABLE MONITOR OUTPUT =========================================== ————————————– TABLE: name SYS_FOREIGN…47, appr.key vals 1,…and the SHOW PROCESSLIST connections…
Comment: Should you name indexes while doing ALTER TABLE ?
May 30, 2008 By 10 Comments
… to discover that mySQL does not support naming the primary key. If you select …KEY `pk_testing` (`testing_id`) ); You table will be happily created with primary key, but when you do a show… PRIMARY KEY (`testing_id`) ) As far as naming conventions, I usually name my foreign key constraints fk__…
