June 18, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (… # ######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`),…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

… gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you missed it, you… in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the…better random choice. Another workaround may be to add a column to the table, and populate the…

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

Post: Is Synchronous Replication right for your app?

May 14, 2013 By 18 Comments

… Innodb is a single row (well, the PRIMARY KEY index entry for that row).  This means…standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication?…joins a group, you run a transaction that adds the relationship row to users_groups, but…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: , , , , , ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… make primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key appended… will need to add indexes manually. Q: What are some methods to overcome vastly differing cardinality on a primary key. After running an…

Filed Under: Insight for DBAs, MySQL

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… Duration | Query | +———-+————-+————————————————————-+ | 1 | 46.78110075 | ALTER TABLE test add column c char(32) default ‘dummy_text’ | …11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE…

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

Post: Long PRIMARY KEY for Innodb tables

October 3, 2006 By 8 Comments

… key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key… MyISAM tables in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not I use… also wanted to keep system flexible so if we want add more thumbnail sizes we would not need to change anything…

Filed Under: Insight for DBAs Tagged With: ,

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… foreign keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY (`parent… mysql> update child set parent_id=1 and v=’BBB’; ERROR 1452 (23000): Cannot add or update a child row: a foreign key…

Filed Under: Insight for DBAs, MySQL

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create table t\G [...] KEY `name` (`name`), KEY…secondary keys contains the primary key column. Let’s see the following example: mysql> show create table t; [...] PRIMARY KEY (`i`), KEY `key_name`…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

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

March 21, 2012 By 10 Comments

… the primary key values, and then instead of point primary key lookups, a range lookup is performed on the primary key by using the sorted primary key values…: 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) Buffer… fact MRR adds extra sorting overhead which means that the queries are just a bit slower as compared to MySQL 5.5…

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

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… InnoDB need to add a hidden column (similar to GEN_CLUST_INDEX when you don’t define a PRIMARY KEY, I assume) when… get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000…

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