Jeśli stosujemy funkcję CONCAT() jako jeden z parametrów JOIN’u, należy pamiętać o pewnych jej cechach. Jeśli wszystkie jej parametry są niebinarnymi ciągami znaków, wynik jej działania także jest niebinarnym ciągiem znaków. Jeśli jakiś paramert jest w postaci binarnej, lub jest to jakaś wartość liczbowa, która z automatu jest konwertowana na postać binarną – wynikiem działania funkcji jest binarny ciąg znaków.

Jakie to ma znaczenie? Załóżmy następującą strukturę bazy danych:

mysql> SHOW CREATE TABLE tab1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab1  | CREATE TABLE `tab1` (
`id` int(11) NOT NULL DEFAULT '0',
`data` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE tab2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab2  | CREATE TABLE `tab2` (
`id` int(11) NOT NULL DEFAULT '0',
`id_1` varchar(40) DEFAULT NULL,
`data` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_id_1` (`id_1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

Krótką pętelką w bashu zapełniamy tabele:

for i in `seq 1 50` ; do mysql -e "INSERT INTO tab1 VALUES ('${i}', 'xxxxxxxx');" test ; done
for i in `seq 1 50` ; do mysql -e "INSERT INTO tab2 VALUES ('${i}', 'kol_${i}', 'xxxxxxxx');" test ; done

W efekcie w tabelach mamy wpisy typu:

mysql> SELECT * FROM tab1 LIMIT 2;
+----+----------+
| id | data     |
+----+----------+
|  1 | xxxxxxxx |
|  2 | xxxxxxxx |
+----+----------+
2 rows in set (0,00 sec)

mysql> SELECT * FROM tab2 LIMIT 2;
+----+-------+----------+
| id | id_1  | data     |
+----+-------+----------+
|  1 | kol_1 | xxxxxxxx |
|  2 | kol_2 | xxxxxxxx |
+----+-------+----------+
2 rows in set (0,00 sec)

Załóżmy, że konieczne jest wykonanie następującego LEFT OUTER JOIN’a:

mysql> EXPLAIN SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON tab2.id_1 = CONCAT('kol_',tab1.id) WHERE tab1.id IN ('30', '23','32');
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tab1  | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
|  1 | SIMPLE      | tab2  | ALL   | idx_id_1      | NULL    | NULL    | NULL |   50 |             |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set (0,00 sec)

Jak widać, optimizer nie wykorzystał indeksu idx_id_1 nałożonego na pole id_1. W efekcie konieczne jest przeskanowanie całej zawartości tabeli tab2. Dzieje się tak dlatego, że w funkcji CONCAT() jako jeden z argumentów podajemy pole tab1.id, które jest typu INT i efektem działania tej funkcji jest binarny string. Jak to obejść? Rzutujemy INT na CHAR:

mysql> EXPLAIN SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON tab2.id_1 = CONCAT('kol_',CAST(tab1.id AS CHAR)) WHERE tab1.id IN ('30', '23','32');
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | tab1  | range | PRIMARY       | PRIMARY  | 4       | NULL |    3 | Using where |
|  1 | SIMPLE      | tab2  | ref   | idx_id_1      | idx_id_1 | 43      | func |    5 |             |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
2 rows in set (0,00 sec)

To samo zapytanie, w przypadku gdy CONCAT() zwraca string w postaci niebinarnej, korzysta poprawnie z indeksu idx_id_1. Wraz ze wzrostem ilości danych w bazie, różnica w szybkości działania tych zapytań będzie znacznie wzrastać.

Co wyraźnie trzeba zaznaczyć, stosowanie tego typu rozwiązań (JOIN  z CONCAT()) w zapytaniach nie świadczy zbytnio o poprawności struktury bazy danych. Jeśli to tylko jest możliwe, należałoby tego typu pomysłów unikać – nigdy nie będzie to tak wydajne zapytanie jak JOIN pomiędzy „zwykłymi” polami. Jasne jest jednak dla mnie, że często (i z różnych względów) nie da się takich konstrukcji uniknąć. Stąd ten post.