June 18, 2013

Post: MySQL Optimizer and Innodb Primary Key

October 3, 2006 By 9 Comments

… knows Innodb tables is clustered by primary key in the sense it would not be faster to do external filesort than to do lookups in primary key order: mysql> explain select * from innodb…

Filed Under: Insight for DBAs Tagged With: ,

Post: Innodb locking and Foreign Keys

December 12, 2006 By 2 Comments

… work with foreign keys in Innodb. I’ve only done a quick check so could be missing some details. All Innodb Foreign Key related operations… not part of FOREIGN KEY constraint – no foreign key originated locks will happen. However if you update PRIMARY KEY value in the child table the…

Filed Under: Insight for DBAs Tagged With:

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For each pk_column value in step 1 do: SELECT non_key…

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

Post: InnoDB: look after fragmentation

November 5, 2009 By 12 Comments

…-primary options to force dump in primary key order. So notes to highlight: InnoDB fragmentation may hurt your query significantly, especially when data is not in… by secondary key is much more likely than by primary key, and you cannot really control it (tough it is possible in XtraDB / InnoDB-plugin…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

… NOT NULL default ’0′, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `country_id` (`country_id`,`state_id`,`city`) ) In this benchmark we used only… query with access by primary key. InnoDB is faster than MyISAM by 6-9%. Falcon shows very bad scalabilty. READ_KEY_POINT Query: SELECT…

Filed Under: Benchmarks Tagged With: ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1… depending on Innodb ordering of data by primary key inside indexes they can become significantly affected. Optimizer behavior may be different in different MySQL…

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

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

…/pages=91% It says that PRIMARY key (which is the table by itself, as InnoDB is clustering data by primary key) takes 497839 pages ( 16KB… as the primary key, but a lot of this is to be expected. In a lot of cases we insert into the primary key in order which makes things very predictable, but the inserts into the secondary key…

Filed Under: Percona Software Tagged With: , ,

Post: MyISAM Scalability and Innodb, Falcon Benchmarks

October 12, 2007 By 10 Comments

…: eq_ref possible_keys: PRIMARY,id key: PRIMARY key_len: 4 ref: scale.t2.t1_id rows: 1 Extra: Using where 2 rows in set (0… 493 16 498 InnoDB both performs much better in this case (not surprisingly as there is a lot of primary key lookups) but its scalability is not perfect giving less than 2x in peak which happens to…

Filed Under: Uncategorized Tagged With: , , , ,

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

February 26, 2013 By 1 Comment

… `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 We also have identical tables created in 5.5…

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

Post: Logging Foreign Key errors

September 27, 2012 By Leave a Comment

… in the foreign key in table is “PRIMARY” See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. InnoDB…

Filed Under: MySQL
« Older Posts
Newer Posts »