…MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query… with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on the auto_… This trick was discovered by Michael Rikmas when we were working on a Consulting case…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…method uses the changelogs collected by FlexCDC to update the snapshot to reflect the changes that … ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view is ‘enabled’, the contents are created: mysql> … simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…ignore them for a moment. The amount of resources system has will place a limit on amount of queries…be consuming all CPU when we’re running 16…query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql…ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT_COMMANDS: 181243 UPDATE_…
Post: Filtered MySQL Replication
November 7, 2007 By 7 Comments
… from backup. There are however some cases when you really do not need events in binary …-ignore-table options which act differently. Instead of looking at the query and analyzing which tables this query…traditional pre MySQL 5.0 way to break such replication is also using multi-table update or delete…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index… now the same: mysql [localhost] {msandbox} (test) > update idxtest set i2=i1; Query OK, 10900996 …the hint in MySQL which would allow forcing using index merge when MySQL does not…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…{msandbox} (test) > DELETE IGNORE FROM t1 WHERE t1_c1 BETWEEN 301 AND 500; Query OK, 100 …*************************** Level: Error Code: 1451 Message: Cannot delete or update a parent row: a foreign key …IGNORE, be critical about your data 2) use ROW* based replication. When using the latter, MySQL …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…your script can figure out which files it can ignore. What we’re doing here starts to …of emulates row-based replication in a way. When you’re updating big aggregate tables, don’t work with …complex queries. Percona is not tied to MySQL, although we’re most famous for our knowledge about it. When …
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into table … columns stored “-0″ in MySQL 5.0 but it was displayed as “0″ when data loaded to Percona Server 5.1. This was not the issue for application and could be ignored. …
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…New Features InnoDB Fake Changes When restarting a slave server …for “INSERT“, “UPDATE“ and “DELETE“ statements but not updating them (Fake Changes…so –innodb-optimize-keys should ignore foreign key constrains. #859078 (Alexey…fix for MySQL bug #53761 (Wrong estimate for RANGE query with compound…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… Repositories). Based on MySQL 5.5.16, …Features InnoDB Fake Changes When restarting a slave …“INSERT“, “UPDATE“ and “DELETE“ statements but not updating them (Fake…–innodb-optimize-keys should ignore foreign key constrains. #859078… Kopytov). QUERY_RESPONSE_TIME doesn’t respect QUERY_RESPONSE_…
