… query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But there’s a finite amount of engineer… STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index-scan of 7 rows in the `kind_type… upcoming Percona Training classes in North America and Europe. Join Percona and the MySQL community at our events. The next one is…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… regions. The flights represent the type of investigations and drill-downs…believe the adaptive hash index is improving performance of InnoDB significantly during …/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb… read_rnd_buffer_size=8M join_buffer_size=8M default_tmp…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… FlashCache and vCache both show improvements, but it’s not …type=pareto \ –forced-shutdown=1 –max-time=7200 –max-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql…join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
….5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post… to BKA MySQL 5.6, and 965x less time as compared to BNL MySQL 5.5. So hash join gives us an improvement by… workload. Again here hash join beats BKA and BNL comprehensively. Hash join outperforms the other join types when you are joining a small table with…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… to SELECT statements. Refresh type Aggregation Outer join All SQL functions Built …| <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer_sales … NOW(), it but still provides improved performance during refresh by accessing summarized…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…improve join performance. As I told you above, when table t1 would be joined…MySQL has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: role_id…new features of MySQL 5.6, and actually is a great improvement over MySQL 5.5. For …tools that MySQL provides with its last GA release? Then I invite you to join …
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… upgraded from MySQL 5.1 to Percona Server 5.5 and instead of expected performance improvement you see … times or even workload consisting of several query types. Though it is rather rare for this to … old one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever …
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…pre-generated table and use the join to retrieve the rows from original table: mysql> show create table dl \G … Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key_len…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…query execution plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered …query times between MySQL 5.5 and MySQL 5.6/MariaDB 5.5 This is another area of improvement in …my next post which will be on Block Access Join Algorithms. Conclusion There is a huge speedup when …
