June 19, 2013

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

May 31, 2013 By Leave a Comment

…Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1831 Message: Duplicate index ‘col2_2′ defined…MySQL 5.7 the checks will be stricter: in the default SQL mode, a duplicate index will throw a warning instead of a…

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

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

February 26, 2013 By 1 Comment

…73 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +———+——+————————————————–+ | Level | Code | Message | …that if you define a column and a unique key as …a time. If you try it, this is what happens: mysql…

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

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows affected (0.01 sec) Records: 543 Duplicates… the scores did change slightly. What about a Boolean mode query? The docs …defining a FULLTEXT KEY, you can use the “WITH PARSER” modifier to specify the name of a…

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

Post: Duplicate indexes and redundant indexes

August 17, 2006 By 38 Comments

… duplicate indexes so its the time to speak about these a bit. So what is duplicate index ? This is when table has multiple indexes defined… make sense while did not notice it was already created. MySQL is very permissive and allows you to create many keys… columns in index is significant, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when ? I call…

Filed Under: Insight for DBAs Tagged With:

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

October 27, 2012 By 6 Comments

… a secondary UNIQUE KEY. The queries being run against this table were almost exclusively INSERT … ON DUPLICATE…were defined, and the PRIMARY KEY is always going to be considered as being defined first. So, MySQL … by the optimizer than a UNIQUE on the secondary index. So what do we learn here? …

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

Post: Statement based replication with Stored Functions, Triggers and Events

December 16, 2011 By 4 Comments

… use it as a PITR recovery. Here we will see what is the behavior of the MySQL when it…a problem to take in account or in some other cases help us in our infrastructure. Is possible to define…’t duplicate data. The statements inside the Event are logged and not the Event itself. Example: mysql> create…

Filed Under: MySQL

Post: Percona XtraDB Cluster Feature 1: High Availability

January 17, 2012 By 20 Comments

… that you can convert your existing MySQL / Percona Server into Node and … have several duplicates of data. For 3 nodes – 3 duplicates This basically defines how … nodes. The biggest question there, what will happen when the Node joins… on nodes. Each node contains a cache, ring-buffer, (the size is…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Fishing with dynamite, brought to you by the randgen and dbqp

November 16, 2011 By Leave a Comment

…define a realm of possible queries (provided you did it right…the zen of grammar writing is a…what shakes out under a certain type of stress. Another win is that a well-crafted grammar can be used for a….py –default-server-type=mysql –basedir=/path/to/… were unable to duplicate this bug otherwise. …

Filed Under: Benchmarks, MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… sec) Records: 0 Duplicates: 0 Warnings: 0 Let’s trigger a table rebuild by adding a new column and see what execution time is…: 4194304 Duplicates: 0 Warnings: 0 mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE… KEY defined, because in this case InnoDB picks such an index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

…. The default value is 500…” What this means from a practical perspective is that each query … Records: 3 Duplicates: 0 Warnings: 0 And now on to a more interesting scenario: foreign keys mysql> CREATE TABLE…available to most users? INSERT w/PRIMARY KEY defined: Number of rows inserted – 1 INSERT w/…

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