… presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed …the primary key solution for random selection only work when the IDs for …column, and reinitializing the sequence after making certain insert/update/delete operations. If you have a dataset that changes…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… mixed CHAR and VARCHAR columns would be used in … #1130888 (Seppo Jaakola). If MySQL replication threads were started…would get overwritten, and any changes (such as username and …UPDATE[18-04-2013]: There was a RPM packaging regression introduced with the fix for bug #710799. This regression only…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL…’t change the law, you can only …UPDATE achievements SET count = $last_count where achievement = ‘killed_troll’”; } change your schema In Example 2, above, how above moving the ‘joined’ column…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… up a dedicated MySQL slave which has log_slave_updates=1 and binlog_format=row to process SBR changes from a MySQL master. I… of the changes we made. You will notice that the source table only has two columns, but the changelog contains five. All change logs contain three additional metadata columns: dml_type, uow…
Post: Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
… run an accidental UPDATE statement that changes a row: UPDATE `t1` SET `c2` = ‘tom’ WHERE `c1` = 2; If we examine this UPDATE using the… interest is that the old and new versions of the changed column is stored in the binary log — in this example in… recovery example ONLY works when binlog_format=ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…MySQL 5.5.3 Prior to MySQL 5.5.3 a statement that opened a table only…///+AIAAAAAAAAAA2JhcvgCAAAAAAAAAANob3A= ‘/*!*/; ### UPDATE test.test ###…changed, starting MySQL 5.5.3. Let’s see how this works now. Metadata Locking behavior starting MySQL…46.78110075 | ALTER TABLE test add column c char(32…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data change needs a table rebuild… to update all columns whenever they have changed or not: mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) mysql…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… changelogs collected by FlexCDC to update the snapshot to reflect the changes that have happened in the…ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select … it examines only the rows that changed. This exceptional ability to look at only what changed, even for…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…’ve changed the table to make columns i1 and i2 highly correlated. In fact they are now the same: mysql [localhost] {msandbox} (test) > update…’ve seen before. What if we leave MySQL no choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select avg…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…index tree. I’ve updated column to have bunch of long random strings update sbtest set c=concat…in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row … this benefit only applies to the case when you’re adding/dropping/changing columns as if you…
