You are here

Be careful when joining on CONCAT

MySQL Performance Blog - Tue, 16/10/2007 - 9:32pm

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:

PLAIN TEXT SQL:
  1. mysql> EXPLAIN
  2.     -> SELECT
  3.     ->  tb1.*
  4.     -> FROM tb2
  5.     -> STRAIGHT_JOIN tb1
  6.     -> WHERE
  7.     -> (
  8.     ->    tb1.vid LIKE 'prefix-%' AND
  9.     ->    tb1.vid = CONCAT('prefix-', prefix.ID) AND
  10.     ->    tb2.gid = 1337
  11.     -> ) ORDER BY tb1.title ASC LIMIT 4\G
  12. *************************** 1. row ***************************
  13.            id: 1
  14.   select_type: SIMPLE
  15.         TABLE: tb2
  16.          type: ref
  17. possible_keys: gid
  18.           KEY: gid
  19.       key_len: 4
  20.           ref: const
  21.          rows: 53
  22.         Extra: USING WHERE; USING TEMPORARY; USING filesort
  23. *************************** 2. row ***************************
  24.            id: 1
  25.   select_type: SIMPLE
  26.         TABLE: tb1
  27.          type: ALL
  28. possible_keys: vid
  29.           KEY: NULL
  30.       key_len: NULL
  31.           ref: NULL
  32.          rows: 570518
  33.         Extra: USING WHERE
  34. 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:

PLAIN TEXT SQL:
  1. mysql> EXPLAIN
  2.     -> SELECT
  3.     ->  tb1.*
  4.     -> FROM tb2
  5.     -> STRAIGHT_JOIN tb1
  6.     -> WHERE
  7.     -> (
  8.     ->    tb1.vid LIKE 'prefix-%' AND
  9.     ->    tb1.vid = CONCAT('prefix-', CAST(prefix.ID AS CHAR)) AND
  10.     ->    tb2.gid = 1337
  11.     -> ) ORDER BY tb1.title ASC LIMIT 4\G
  12. *************************** 1. row ***************************
  13.            id: 1
  14.   select_type: SIMPLE
  15.         TABLE: tb2
  16.          type: ref
  17. possible_keys: gid
  18.           KEY: gid
  19.       key_len: 4
  20.           ref: const
  21.          rows: 53
  22.         Extra: USING WHERE; USING TEMPORARY; USING filesort
  23. *************************** 2. row ***************************
  24.            id: 1
  25.   select_type: SIMPLE
  26.         TABLE: tb1
  27.          type: ref
  28. possible_keys: vid
  29.           KEY: vid
  30.       key_len: 101
  31.           ref: func
  32.          rows: 2
  33.         Extra: USING WHERE
  34. 2 rows IN SET (0.00 sec)

Much better now.

Entry posted by Aurimas | No comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks