June 19, 2013

Post: Covering index and prefix indexes

November 23, 2006 By 5 Comments

…. Note: MySQL is however smart enough to make sure prefix specified is actually prefix, not the full key length. If you would specify key length of 10 in this case it will convert it to the full key instead of prefix key. If you would use length…

Filed Under: Insight for DBAs Tagged With:

Post: Data compression in InnoDB for text and blob fields

May 30, 2012 By 2 Comments

…WHERE `id` < 1000 compare this to: SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE `id` <...MySQL 5.5) you can now leverage table compression by specifying ROW_FORMAT=COMPRESSED.  In our case we only wanted MySQL… is table specific, ROW_FORMAT and KEY_BLOCK_SIZE directives are passed via…

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

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

… each MV could have drifted by a different length of time. A view which has drifted out … the schema is not specified. The MV will be placed into whatever schema is specified as the first argument … It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as rank, customer_id…

Filed Under: Insight for DBAs, MySQL

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

…) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY (ancestor, descendant) ) ENGINE=InnoDB; Because there isn… (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause…

Filed Under: Insight for Developers, MySQL

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…information about last failed foreign key constraint: ———————— LATEST FOREIGN KEY ERROR ———————— 060717 4:29…it will be zero. History list length 6 is number of unpurged transactions…sleep_delay variable. Value is specified in microseconds. mysql tables in use 1, locked…

Filed Under: Insight for DBAs Tagged With:

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

March 4, 2013 By 7 Comments

…’s default ft_min_word_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title… phrases that include initials, numbers, or any other string of length less than 3 (the default), I think you’re going… rabbit hole. When defining a FULLTEXT KEY, you can use the “WITH PARSER” modifier to specify the name of a UDF which…

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

Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.

May 29, 2006 By 48 Comments

… ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but … and back. This allows to save on field length significantly by using 4 bytes instead of 15 …() in insert clause but this would require to specify list of columns which we skipped for sake …

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

Post: Hidden columns of query_review_history table

August 28, 2012 By Leave a Comment

…digest to process a MySQL slow query log and store historical…, which are a collection of key/value pairs called attributes. You’…set the hostname you need to specify it in the –filter option. For…pass,u=percona \ –filter=” \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\”$HOSTNAME…

Filed Under: Insight for DBAs, MySQL Tagged With: