Be Careful when Joining on CONCAT

October 16, 2007

Author

Aurimas Mikalauskas

Insight for Developers

Share this Post:

The other day I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT(‘prefix-‘, tb2.id) with tb1.vid – indexed varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use key, forced a different join order – it did not want to use tb1.vid index for it. And no surprise it was way too slow, the number of rows analyzed was really huge:

mysql&gt; EXPLAIN<br>    -&gt; SELECT<br>    -&gt;  tb1.*<br>    -&gt; FROM tb2<br>    -&gt; STRAIGHT_JOIN tb1<br>    -&gt; WHERE<br>    -&gt; (<br>    -&gt;    tb1.vid LIKE 'prefix-%' AND<br>    -&gt;    tb1.vid = CONCAT('prefix-', tb2.ID) AND<br>    -&gt;    tb2.gid = 1337<br>    -&gt; ) ORDER BY tb1.title ASC LIMIT 4G<br>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tb2<br>         type: ref<br>possible_keys: gid<br>          key: gid<br>      key_len: 4<br>          ref: const<br>         rows: 53<br>        Extra: Using where; Using temporary; Using filesort<br>*************************** 2. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tb1<br>         type: ALL<br>possible_keys: vid<br>          key: NULL<br>      key_len: NULL<br>          ref: NULL<br>         rows: 570518<br>        Extra: Using where<br>2 rows in set (0.00 sec)<br>

1

mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE 'prefix-%' AND -> tb1.vid = CONCAT('prefix-', tb2.ID) AND -> tb2.gid = 1337 -> ) ORDER BY tb1.title ASC LIMIT 4G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb2 type: ref possible_keys: gid key: gid key_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1 type: ALL possible_keys: vid key: NULL key_len: NULL ref: NULL rows: 570518 Extra: Using where 2 rows in set (0.00 sec)

Then I took a look at MySQL manual and here’s a short quote about CONCAT:

…If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast…

OK, let’s check if that really helps:

mysql&gt; EXPLAIN<br>    -&gt; SELECT<br>    -&gt;  tb1.*<br>    -&gt; FROM tb2<br>    -&gt; STRAIGHT_JOIN tb1<br>    -&gt; WHERE<br>    -&gt; (<br>    -&gt;    tb1.vid LIKE 'prefix-%' AND<br>    -&gt;    tb1.vid = CONCAT('prefix-', CAST(tb2.ID AS CHAR)) AND<br>    -&gt;    tb2.gid = 1337<br>    -&gt; ) ORDER BY tb1.title ASC LIMIT 4G<br>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tb2<br>         type: ref<br>possible_keys: gid<br>          key: gid<br>      key_len: 4<br>          ref: const<br>         rows: 53<br>        Extra: Using where; Using temporary; Using filesort<br>*************************** 2. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tb1<br>         type: ref<br>possible_keys: vid<br>          key: vid<br>      key_len: 101<br>          ref: func<br>         rows: 2<br>        Extra: Using where<br>2 rows in set (0.00 sec)<br>

1

mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE 'prefix-%' AND -> tb1.vid = CONCAT('prefix-', CAST(tb2.ID AS CHAR)) AND -> tb2.gid = 1337 -> ) ORDER BY tb1.title ASC LIMIT 4G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb2 type: ref possible_keys: gid key: gid key_len: 4 ref: const rows: 53 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: tb1 type: ref possible_keys: vid key: vid key_len: 101 ref: func rows: 2 Extra: Using where 2 rows in set (0.00 sec)

Much better now.