June 19, 2013

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

May 31, 2013 By Leave a Comment

…); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create… example, if one of the two indexes was unique or a foreign key. This is the output of our tool when run…

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

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… between tables versus observing queries only.  An additional benefit …mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY…columns flagged ‘nullable’ and ‘must be unique‘ (woops!), single column tables, incrementing…

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

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

…queries for quality control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO test.MY_KEY…’, COLUMN_NAME = ‘order_id’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, REFERENCED_TABLE_SCHEMA = ‘ecommerce’, REFERENCED_TABLE_NAME …

Filed Under: Insight for DBAs, MySQL

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

…UNIQUE KEY. The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY… table to illustrate the situation: CREATE TABLE update_test ( id INT…MySQL checks our INSERT, sees that the next auto-inc value is available, and claims it, but then it checks the UNIQUE KEY…

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

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

… Shard-Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL…. Then, the airport information was extracted: create table dim_airport( airport_id int auto_increment primary key, unique key(airport_code) ) as select Origin…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

… then I create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0… get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000… define a column and a unique key as they suggest, your data won’t load due to unique key constraint violations unless you also…

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…queries. This will use fast primary key for some queries and use key…key as UNIQUE this has benefits and drawbacks. Benefit being you can get extra optimizations by optimizer knowing index is UNIQUE…creating an index in every table you create? Example: CREATE…

Filed Under: Insight for DBAs, MySQL

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

… auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this…new value to the column. Prior to MySQL 5.1.22 InnoDB used a method…Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY…

Filed Under: MySQL

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…key_checksblogpercona.commanual ft_boolean_syntaxblogpercona.commanual ft_max_word_lenblogpercona.commanual ft_min_word_lenblogpercona.commanual ft_query…

Filed Under: Insight for DBAs, MySQL

Post: Distributed Set Processing with Shard-Query

May 14, 2011 By 27 Comments

…” is a SET created by the …KEY UPDATE is the same. All of the fastest incremental materialized view optimizations can be applied. Shard-Query…right now only MySQL storage nodes …UNIQUE CHECK * on the base table: `origin_airport_id` * storage node result set merge optimization enabled: ON DUPLICATE KEY…

Filed Under: MySQL
« Older Posts