June 19, 2013

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

May 31, 2013 By Leave a Comment

…MySQL Performance Blog, we’ve been discussing the several new features that MySQL…potentially impact our performance, and should be avoided. By the way, … column definition in the same order. In other words, it checks… types: # `col2` int(11) default null # `col3` varchar(200) default null # To remove …

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… “`id` = 5″ and “`id` IN (5)” regarding indexes and performance? A: Recent MySQL versions are smart enough to convert id IN (5… order by on another column. do i need to add the index on column using order by clause. A: If index is used for ORDER BY… Trick “Unionizing Order by” described in presentation. Q: what is the impact on indexing to use wider UUID such as VARCHAR(36) instead…

Filed Under: Insight for DBAs, MySQL

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

March 11, 2013 By 7 Comments

…MySQL 5.6 server, rebooted and verified again.  Same issue.  This was very different from MySQL 5.5.30, which performs… group by 1,2 ) sq order by pct_buffer_…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…) ORDER BY s.name limit 10000,5; Result time(mean): 26.0854793 As you can see, ENUM and VARCHAR show close performance, while join performance…: 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: Analyzing air traffic performance with InfoBright and MonetDB

October 2, 2009 By 50 Comments

…varchar(10) DEFAULT NULL, `TotalAddGTime` varchar(10) DEFAULT NULL, `LongestAddGTime` varchar(10) DEFAULT NULL, `DivAirportLandings` varchar(10) DEFAULT NULL, `DivReachedDest` varchar(10) DEFAULT NULL, `DivActualElapsedTime` varchar…mysql -S /tmp/mysql… BY carrier ORDER BY 2 DESC [ “WN”, …performance…

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

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

March 4, 2013 By 7 Comments

… when performed against InnoDB data, OR that the results returned by InnoDB FTS…mysql: SELECT id, title, MATCH(title, body) AGAINST (‘corporation commission forms’ IN NATURAL LANGUAGE MODE) AS score FROM seo_test_myisam ORDER BY…, “When is a VARCHAR Not Really a VARCHAR?” Thinking that maybe this …

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

Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

September 17, 2007 By 14 Comments

… ( `grp` int(10) unsigned NOT NULL, `slack` varchar(50) DEFAULT NULL ) ENGINE=MyISAM … if MySQL does not allow us to use ORDER BY together with GROUP BY WITH ROLLUP…MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and use of MySQL…

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 …performance. e.g. mysql> select id from archive_table order by id desc limit 1; +———+ | id | +———+ | 1143883 | +———+ 1 row in set (2.04 sec) mysql…

Filed Under: MySQL Tagged With: , ,

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

December 14, 2012 By 6 Comments

…performance win, perhaps. Sometimes it is. Other times, it is a major performance killer. It’s fairly straightforward to tell when MySQL… UNSIGNED NOT NULL DEFAULT 0, username VARCHAR(20), … other columns here … PRIMARY KEY(…id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id…

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

Post: A workaround for the performance problems of TEMPTABLE views

May 19, 2010 By 7 Comments

… as a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then it can use the MERGE algorithm…c1, performance is not good. MySQL is scanning the entire table to produce the result: mysql> select …in between those two extremes: mysql> create table params ( view_name varchar(250), param1_val int…

Filed Under: Insight for Developers, MySQL Tagged With: , , ,
« Older Posts