… C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | …-29 dump.sql DROP TABLE IF EXISTS `C…KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql… dropping 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 …
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…drop index ‘fk_deptid’: needed in a foreign key constraint ============= Below is scenario. drop table if exists emp; drop…at the end) mysql> source list.sql +————–+————+—————–+—————–+————-+———————–+————————+ | table_schema…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…the pessimistic delete, if the page data size drops below this limit, merging it to… ha_innodb.cc in 5.1: static MYSQL_SYSVAR_ULONG(thread_concurrency, srv_thread_…foreign key constraints)? Any concrete examples are appreciated. HT: Yes, it is sufficient that a foreign key and the referenced key…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
…m currently working on a web project using MySql, Apache and Php. The database has a … task. After those months pass, I’ll drop all those tables, and rebuild them once …KEY (E), CONSTRAINT key_A FOREIGN KEY (A) REFERENCES ATable(A) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT key_B FOREIGN KEY…
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 you …MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql… by 10-30% MyISAM drops down with 128-256 threads READ_KEY_RANGE_LIMIT Query: SELECT …
Post: Converting Character Sets
March 17, 2009 By 22 Comments
…) Records: 0Â Duplicates: 0Â Warnings: 0 mysql> SHOW CREATE TABLE `t1`\G *************************** 1. row …be run on each table: 1) Drop FULLTEXT indexes 2) Convert target columns… of string foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys anyway …)…
Comment: Performance problem with Innodb and DROP TABLE
February 5, 2011 By 23 Comments
Jim, With MySQL 5.5, if you don’t have foreign keys or triggers, then a TRUNCATE will simply drop the table and recreate…. Deleting all rows from the table could incur significant expense. MySQL 5.5 does have metadata locking which will eliminate holding… it improves performance for you. MySQL 5.5 will still hold the kernel mutex during a drop, but this is fixed in…
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, …output, you just DROP the table …
Post: Intro to OLAP
July 12, 2010 By 10 Comments
… particularly OLAP (or online analytical processing) tools using MySQL and other free open source software. OLAP … have to write queries or drag and drop a report together in order to analyze …and customer_id. These are often defined as foreign keys from the fact table to the dimension tables…
