June 20, 2013

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

May 31, 2013 By Leave a Comment

…0.00 sec) In previous versions of MySQL, you can create two indexes with the same columns (in the same order…impact our performance, and should be avoided. By the way, a good practice to … # Column types: # `col2` int(11) default null # `col3` varchar(200) default null # To remove this duplicate index…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…like ‘%ssb%’ group by 1,2 ) sq order by pct_buffer_pool desc; …0; mysql> set global innodb_old_blocks_time=0; Query OK, 0 rows affected (0.00 sec) mysql…varchar(25), C_Address varchar(25), C_City varchar(10), C_Nation varchar(15), C_Region varchar(12), C_Phone varchar(15), C_MktSegment varchar…

Filed Under: MySQL Tagged With: , , , , ,

Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By 30 Comments

…_enum ORDER BY state limit 10000, 5; Result time(mean): 0.077549 2) Results for VARCHAR: select SQL_NO_CACHE city from cities_varchar ORDER BY state limit 10000, 5; Result time(mean): 0.0854793…: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY…

Filed Under: Benchmarks Tagged With: , , , , , , ,

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

March 4, 2013 By 7 Comments

…: mysql: SELECT id, title, MATCH(title, body) AGAINST (‘corporation commission forms’ IN NATURAL LANGUAGE MODE) AS score FROM seo_test_myisam ORDER BY…_list2 ( value VARCHAR(18) NOT NULL DEFAULT ”) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql: SET GLOBAL innodb…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0, user_type TINYINT UNSIGNED NOT NULL DEFAULT 0, username VARCHAR(20), … other columns… user_id > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE…_type=2 AND user_id > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1\G *************************** 1. row *************************** id: 1…

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

Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

September 17, 2007 By 14 Comments

…CREATE TABLE `dt` ( `grp` int(10) unsigned NOT NULL, `slack` varchar(50) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And … order by (and limit): mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp with rollup; +——-+———+ | grp | cnt | +——-+———+ | 0 | …

Filed Under: Benchmarks, Insight for Developers Tagged With:

Post: Adventures in archiving

February 11, 2013 By 16 Comments

… `archive_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dat` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=ARCHIVE DEFAULT CHARSET….04 sec) mysql> select id from compressed_row order by id desc limit 1; +———+ | id | +———+ | 1143883 | +———+ 1 row in set (0.00 sec) Also…

Filed Under: MySQL Tagged With: , ,

Post: A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By 8 Comments

…` varchar(100) DEFAULT NULL, `spaceid` int(11) NOT NULL, PRIMARY KEY (`spaceid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0… ] do CURRENT_SPACEID=`mysql -u root -B -N -e “select spaceid from test.tables where spaceid >= $CURRENT_ID order by spaceid limit 1;”` if [ "a$CURRENT_SPACEID" == "a" ]; then exit fi CURRENT_TABLENAME=`mysql -u root -B -N…

Filed Under: MySQL

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

…_role_id` int(11) DEFAULT NULL, `note` varchar(250), `nr_order` int(11) DEFAULT NULL, `role_id` int…just 266 (5.6), reducing the executing time by almost 5 times. Pro tip: make sure …0.00 sec) I reported this issue to Oracle and they confirmed that this is the intended/current status of the MySQL…

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: , ,

Post: Common MySQL traps webinar questions followup

January 17, 2013 By 1 Comment

…(with long_query_time = 0) for some time during …order for an e-commerce application. The status may be ‘payed’, ‘shipped’, ‘canceled’ or ‘archived’. If you use a string, a VARCHAR…true of all versions of MySQL? If not which versions?…any logical backup facility provided by Percona like mysqldump? No, …

Filed Under: MySQL
« Older Posts