… and adaptive hash index are cold for the cold test. All tests were done with no concurrency. The… memory, InnoDB almost always performs better, at least for this workload. Notes MySQL version used: 5.6.11,…_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage_engine=myisam…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… here, and it isn’t always obvious how they will affect your… row (well, the PRIMARY KEY index entry for that row). This…with some metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1); UPDATE groups SET last_joined… be modified. Generally if you use Innodb, you should be able…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…00 | Using where; Using index; Using temporary; Using filesort | …always uses the max values join algorithm. If join_cache_level=4 it always uses BNLH (hash join), if join_cache_level=8 it always uses…joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the joins… rows, index lookup would be performed on t2. Here is where Key-ordered scan comes in. With BKA turned on, the index lookups… 0.16s less as compared to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…with BETWEEN range replaced with IN list was instant 0.00 sec same as the query using join with…unless it is equality. You always have something to learn about … Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index …
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…it.” — Dr. Emmett Brown I populated these tables with enough data to serve our purpose. Our …: Using where; Using index In this case MySQL does not print ref at all, because there is no join, …need to solve. Essentially it always comes down to removing range condition from join one way or another…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
…for small fraction of queries. If you’re joining multiple tables or if you have GROUP BY…with COUNTRY=’USA’ are used in results set which clearly puts query as optimization candidate. It is not always…number of rows used to generate result set – for example any filter which can’t use indexes will make…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…: const rows: 10259274 Extra: Using index condition 1 row in set (0.00 sec) With ICP, the actual …? It seems that whenever the covering index technique is available, this is always preferred over the ICP optimization:…tools that MySQL provides with its last GA release? Then I invite you to join me at …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…with addition of greedy join the problem on looking at too many table join combinations in joins with…1 | SIMPLE | lt | index | NULL | PRIMARY | 4 | NULL | 1600 | Using index | …give you head ache. filesort, using temporary are always in the first line …
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…always advised to have at least 3 nodes (this is the case with PXC but it’s also the same with…index | 1 | | wsrep_ready | ON | only wsrep_local_index defers as expected. Now let’s stop the communication between both nodes (using…-0800-f7c4916d8330, } joined { 7e6d285b-d56d-11e1-0800-2491595e99bb, } …
