… and Expo in San Jose, Calif. It ran from April 10-11 that year.[/caption] Meet New Friends. Each year I… subtle nuances I’m communicating. Each time I throw some trick question into the audience I get someone who is aware…
Post: What I'm looking forward to at Percona Live (MySQL Users Conference)
April 4, 2013 By 1 Comment
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… does not work while I know I used exactly this trick to optimize some nasty queries. It looks like equality propagation… (and in this case you do not even need to trick around equity propagation to make it work) mysql> explain select…_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: Using index *************************** 3. row *************************** id: 1 select_type: SIMPLE…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… even higher. CREATE TABLE `fact` ( `i` int(10) unsigned NOT NULL, `val` int(10) unsigned NOT NULL, KEY `i` (`i`,`val`) ) CREATE TABLE `dim` ( `id` int(10) unsigned NOT NULL auto_increment, `pad` varchar(100) NOT NULL…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… the prefix adaptive hashing would fail here, because the first 10 or so characters usually are the same). Of course UNIQUE… more complicated conditions you will need to use something like Trick “Unionizing Order by” described in presentation. Q: what is the…: Date comparisons work very similar to other comparison and same tricks may apply, for example you may benefit to convert BETWEEN…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… primary key, optimizing table and adding them back takes about 10 minutes, which is close than 20x speed difference and more…’re considering running OPTIMIZE on your tables consider using this trick, it is especially handy when you’re running it on… indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… components to give our customers reliable backups? Think about the 10 example outages listed above. Each tool has it’s strong… with disk space and writes out binlog files sequentially. Tips/Tricks (how we run this): Ensure it stays running, restart it… effectively store long term backups with little additional work Tips/Tricks: –add-header=x-amz-server-side-encryption:AES256 to use…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select_type | table | type | possible_keys | key | key…re not looking to measure. This is actually good trick to use for cases when you do not need…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…minimum row size MyISAM can have in this configuration. This trick however does not work for Innodb which allocates 1 byte…Create_options: Comment: *************************** 3. row *************************** Name: tbool Engine: MyISAM Version: 10 Row_format: Fixed Rows: 2097152 Avg_row_length: 11 …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…. You did simple arithmetic to make the math faster, adding 10 + 10. This is a simple fact, that when you distribute computation… (val) where compressed.cnt >= search_set.cnt; +—–+———-+——+ | val | cnt | cnt | +—–+———-+——+ | -10 | 1 | 1 | | -3 | 1 | 1 | | -2 | 1 | 1 | | 15 | 35417088… a way to do it without using the “search set” trick above. Also, notice that it takes a long time to…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… to an output directory: The –export option is the magic trick that will help us to get a consistent backup with…-11 | | 10008 | 48584 | 1999-03-11 | 2000-03-10 | | 10008 | 52668 | 2000-03-10 | 2000-07-31 | +——–+——–+————+————+ mysql> DELETE FROM salaries WHERE…-11 | | 10008 | 48584 | 1999-03-11 | 2000-03-10 | | 10008 | 52668 | 2000-03-10 | 2000-07-31 | +——–+——–+————+————+ The salary history from the…
