… today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just…, and full query execution time to less than 50ms. Low values, such as 3,4 provided a bit better performance but I… Timour explanation somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… before MySQL has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join algorithm… is that, just increasing the join_buffer_size is not going to provide the best possible query performance. As I mentioned at the…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… were captured when performing the benchmark on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w… not actually mean that queries with MRR are performing badly. The interesting thing is that though both MariaDB and MySQL 5.6 are… improve the performance of secondary key lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… MariaDB as a first-class member of the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions MariaDB in many places… the binary log enabled Faster queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented…
Comment: Introducing new type of benchmark
February 25, 2012 By 16 Comments
… tpcc-mysql for example doesn’t use JOINs, something we know is expensive in the MySQL world,… benchmark performance across the board? With the increasing emergence of drop-in replacements for MySQL like Percona and MariaDB which are either tweaking or flat out overhauling their query…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…for querying: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query OK…_table_alias: o mview_join_condition: NULL mview_join_order: 999 *************************** 2. row …Refreshed both views. Compared the refresh performance and query results. The view type …
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… us now replace the range with IN list in this query: mysql> explain select sum(events) from info where d IN(’2007… you get say thousands of groups at the same time ? Performing such query MySQL has to build (and do lookups) for all combinations… sec same as the query using join with day list table. So we finally managed to get better performance by joining data to yet…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0…. From that information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…application by aggregating query log with mk-query-digest. When I filtered out only queries of this…gain system shows from performance at 1 thread to the peak performance we see the number…do_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… give us some interesting information about how Shard-Query will perform on each database. To do the size…avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’…-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M …
