… this means that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ… is one additional join optimization in MariaDB that I would like to quickly explain here, and that is Hash Joins. Hash Join As I… Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join algorithm only works with equi-joins. Now let me briefly explain how hash join…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… incremented when index lookup is performed. As I explained at the start of the post that traditional index lookup (for non-index-only columns) involves, reading an index record, and… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have to… the join in this case because there is really no join: mysql> EXPLAIN select SQL_NO_CACHE c.city from cities_join c JOIN states… performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… join – exactly what we tried to avoid. It is easy to block equality propagation by using some trivial function: mysql> explain…30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In….0 with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is… | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) This statement obviously will not scan more than 10 rows but EXPLAIN shows…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…. And we have proper indexes on all necessary columns! What does EXPLAIN have to say about this? ***************************…). Also ref shows two columns used in join. SHOW STATUS LIKE ‘Handler_read%’; +———————–+——-+ | Variable_name | Value | +———————–+——-+ …
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… will be read. This is because these tables contain no indexes. There is another interesting thing, and I probably should have… any perceived performance difference between comma join and ANSI JOIN syntax. They are intrinsically the same. Last, EXPLAIN EXTENDED can show you information…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance …seconds and had the following EXPLAIN: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: PRIMARY key: …
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… do Join not on primary key and have query to read data for both tables. If the query would be index covering… lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on… bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you…
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
….vid index for it. And no surprise it was way too slow, the number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…, let’s check if that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…
