June 19, 2013

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

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

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

…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

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

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

Post: Duplicate indexes and redundant indexes

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…

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

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? …

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

… 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…

Post: Percona XtraDB Cluster Feature 1: High Availability

… 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…

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

define a realm of possible queries (provided you did it right…the zen of grammar writing is awhat 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. …

Post: Improved InnoDB fast index creation

… 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

Post: Tuning InnoDB Concurrency Tickets

…. 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/…