June 18, 2013

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

… its `NAME` is PRIMARY. If the primary key is not defined InnoDB will use a unique secondary index as the primary. If there is… need to know what fields form the primary key. The matter is regardless at what position primary key fields are defined in CREATE TABLE…` ( `INDEX_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID…

Filed Under: Insight for DBAs, MySQL Tagged With: , , , , , ,

Post: Benchmarking Percona Server TokuDB vs InnoDB

May 7, 2013 By 32 Comments

…, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite… NULL DEFAULT ’0′, `id` bigint(20) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT ’0′, PRIMARY KEY (`hid`,`mid`,`id`) ) and…, $ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is…

Filed Under: Benchmarks, MySQL, Percona Server Tagged With: , , ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… to replace longish (25-30) indexed varchars with an additional bigint column containing the crc64, and change the indexing to be… unusual setup. Q: Table1 has a primary key. Table2 joins to table1 using Table1′s primary key. Should table2 have an index on the… cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You should pick something else if…

Filed Under: Insight for DBAs, MySQL

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…) unsigned default NULL, PRIMARY KEY (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SYS_INDEXES: CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT… NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) )ENGINE=InnoDB DEFAULT…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET…> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): Incorrect column name ‘fts…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Comment: COUNT(*) vs COUNT(col)

April 25, 2007 By 45 Comments

… | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+————-+——-+—————+———+———+——+——–+————-+ | 1 | SIMPLE | wp_comments | index | NULL | PRIMARY | 8 | NULL | 424672 | Using index…` bigint(20) NOT NULL default ’0′, `user_id` bigint(20) NOT NULL default ’0′, `rajid` bigint(20) unsigned default NULL, PRIMARY KEY (`comment_ID`), UNIQUE KEY…

Filed Under: Insight for Developers Tagged With: ,

Comment: COUNT(*) for Innodb Tables

April 3, 2009 By 32 Comments

…` bigint(21) NOT NULL default ’0′, `is_header` tinyint(1) NOT NULL default ’0′, PRIMARY KEY (`messageblk_idnr`), KEY `physmessage_id_index` (`physmessage_id`), KEY…: 1 select_type: SIMPLE table: dbmail_messageblks type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1930308 Extra: Using index…

Filed Under: Insight for Developers Tagged With: ,

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

… select_type: SIMPLE table: dim_date type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_OrderDateKey rows: 1 Extra…), key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key, S… EXISTS lineorder; CREATE TABLE IF NOT EXISTS lineorder ( LO_OrderKey bigint not null, LO_LineNumber tinyint not null, LO_CustKey int…

Filed Under: MySQL Tagged With: , , , , ,

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

…_author_id` int(10) unsigned NOT NULL, `from_message_id` bigint(20) unsigned NOT NULL, `message_published` timestamp NOT NULL default…’, PRIMARY KEY (`link_id`), UNIQUE KEY `domain_id_2` (`domain_id`,`link_id`), KEY `domain_id` (`domain_id`,`from_site_id`,`message_published`), KEY `revert…

Filed Under: Percona Software Tagged With: , ,

Post: A workaround for the performance problems of TEMPTABLE views

May 19, 2010 By 7 Comments

…(11) DEFAULT NULL, `c2` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c2`), KEY `c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2949071 DEFAULT CHARSET… params ( view_name varchar(250), param1_val int, connection_id bigint, primary key(connection_id, view_name) ) engine=innodb; Query OK, 0 rows…

Filed Under: Insight for Developers, MySQL Tagged With: , , ,
« Older Posts