… you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it’s hard to make…? Since they are all logically the same, I believe it should all translate to the same execution plan. (I know it doesn… STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index-scan of 7 rows in the `kind_type` table…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
…mathew: Wrong, it’s still the same in 5.5. Here is the related output from show … handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app_inventory cai inner join…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… maintained in separate tables and there also exists a users_groups table to define the relationship between them. When someone joins a group… groups SET last_joined=NOW(), last_user_id=100 WHERE id=1; COMMIT; How often might multiple users join the same group? Results… happen to the clients who try to update the same row within the same RTT? This depends on which node in the…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… performance drops scattered throughout the graph. We see the exact same pattern when looking at transaction latency; the baseline numbers are… –num-threads=32 –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand-init=on –report-interval=1… table_open_cache = 10240 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… fill TokuDB tables with 1bln of rows on this SSD, and projected InnoDB performance on this size will be the same or… –test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on… table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case… good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note… default: – backwards compatibility, – the hypothesis that most users have joins with few tables, – it is not clear how far from optimal plans…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the joins are read… buffer pool, then worst case you could be reading the same page multiple times into the buffer pool. So considering this… Hash Join. This join algorithm only works with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two tables…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… KEY (id), KEY state (state) ) ENGINE=MyISAM; 3) Table with INT: CREATE TABLE cities_join ( id int(10) unsigned NOT NULL auto_increment…), KEY state_id (state_id) ) ENGINE=MyISAM; 4) Dictionary table for cities_join: CREATE TABLE IF NOT EXISTS `states` ( `id` tinyint(3) NOT… almost the same, but join query performance is 30% lower. Also note the times themselves – traversing about same amount of rows full table scan…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… hash join beats BKA and BNL comprehensively. Hash join outperforms the other join types when you are joining a small table with a very large table… (BKA). However, in Test C the cost of hash join remains the same but the cost of BKA increases, as there are… are joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join can be achieved when the left table can…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…’t specify target table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL’s multi-table DELETE syntax, to….descendant, supertree.length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths AS subtree WHERE subtree.ancestor = ‘D’ AND supertree.descendant…
