June 18, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

… temporary table to store an interim result set, and then use that to ultimately produce … where optimizing a query automatically would be more costly than just running the query in a suboptimal… presentation today is that sometimes you find unexpected differences in performance like this, so it pays …

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

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

May 31, 2013 By Leave a Comment

… online DDL and several other InnoDB and query optimizer improvements. However, I plan to focus on a…in set (0.00 sec) In previous versions of MySQL, you can create two indexes with the same columns (in… which we at Percona tend to find redundant indexes. And second, because in some cases, we may need…

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

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

… some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. Each set is known as a “flight… is approximately 12GB of data in the largest table. You can find the individual SSB query definitions in my previous blog post. Test… follow-up post about concurrency in another blog post in this series. Regardless, when the working set fits in memory, InnoDB almost always performs…

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

Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released

March 25, 2013 By 2 Comments

… tcpdump), so you shouldn’t have a problem finding queries to run and compare. pt-query-…save results in JSON format (–output=json). This feature is still in development while we determine the optimal JSON…do that, the tool magically set some options and clobbered others, resulting in no way to do …

Filed Under: Percona Software, Percona Toolkit Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…| | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +—————————+———+ 10 rows in set (0.02 sec) mysql> optimize table…the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index_table is …

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

Post: How to find MySQL queries worth optimizing ?

September 11, 2012 By 6 Comments

… find out queries which should be optimized. By looking at pt-query-digest report it is easy to find …1 | | +—-+————-+——-+——–+—————+———+———+————-+——+——-+ 2 rows in set (0.00 sec) In this case we have 2 rows analyzed for each row set which is …

Filed Under: Insight for DBAs, MySQL

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

December 14, 2012 By 6 Comments

… per-session: SET [GLOBAL|SESSION] optimizer_switch=”index_merge_intersection=off”; This is the database equivalent of kicking the door in when you…” for the optimizer to favor or disfavor a particular index or set of indexes so that the query executes in a specific fashion… exactly how the server should be finding and manipulating data, they can be quite effective. In the case of this query, since…

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

Post: ORDER BY ... LIMIT Performance Optimization

September 1, 2006 By 100 Comments

… may be scanned to find 10 rows. So index on (…; Using index | +—-+————-+——-+——+—————+——+———+————-+——+———————————+ 2 rows in set (0.00 sec) In this case there is …Force index if needed In some cases MySQL Optimizer may prefer to…

Filed Under: Insight for Developers Tagged With: ,

Post: Goal driven performance optimization

December 22, 2008 By Leave a Comment

…in many cases to focus only on Server Side optimization – the Client Side Optimization is also quite important in… we have to go and find what queries exactly are causing it….51381316608346 | +——+———-+—————–+——————+ 24 rows in set (2.37 sec) As you can see in this case…

Filed Under: Insight for Developers Tagged With: ,

Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

September 17, 2007 By 14 Comments

…in set (21.12 sec) mysql> select count(*) cnt from dt where slack like “a%”; +———+ | cnt | +———+ | 2352996 | +———+ 1 row in set… much sense to me and I find it quite inconvenient whenever it is… am I looking on reporting performance optimization ? It is for ClickAider project which…

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