June 18, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… so that schema changes are automatically picked up.  …mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY…name=”parent” comments=”Parent table”>
name=”parent_id” primaryKey=”true”>

name…

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

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

May 31, 2013 By Leave a Comment

… but handy improvements – changes and bug corrections – in MySQL 5.6 that …practice to avoid this is always naming your keys with a consistent pattern. … # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY …

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

… on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you … form. Q: Doesn’t the primary key solution for random selection only…If you have a dataset that changes infrequently, then this might be a…the index.  Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On …

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… differ only by first column. Regarding changing primary key to include such column prefix it …primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key…say I have a table named PO that has a primary key of PO # and 2 …

Filed Under: Insight for DBAs, MySQL

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…has mostly been around the new changes in MySQL 5.6 (and that is… > show status like ‘Open%tables’; +—————+——-+ | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 0 … not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the …

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

Post: Using Flexviews - part two, change data capture

March 25, 2011 By 26 Comments

… FlexCDC. It seemed like an appropriate name. The Binary Log is the MySQL log which records changes to tables in the database. FlexCDC… a change log, delete rows and then insert some more of them: mysql> create table > test.demo ( > c1 int auto_increment primary key, > c2 int > ) > engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql…

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

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

…keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…changed or not: mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) mysql> select…with same name again it will need to be able to satisfy foreign key conditions (…

Filed Under: Insight for DBAs, MySQL

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

March 11, 2013 By 7 Comments

… use ANSI JOIN syntax.  No other changes to the queries were made. Test Environment The MySQL versions used at the time of…), 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_Name char…

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

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

March 21, 2012 By 10 Comments

… ‘key_column=x’. You can see that there are a lot of random primary key lookups made. With MRR, then steps above are changed to the following: SELECT key_column, pk_column FROM tbl WHERE key_column… benchmark on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…

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

…(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…: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): Incorrect column name ‘fts… did make one configuration change to the default InnoDB settings for MySQL 5.6, and that was to change innodb_ft_min_token…

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