Bug #29286 Problems with index when using concat function for joining
Submitted: 21 Jun 2007 21:02 Modified: 21 Nov 2007 8:38
Reporter: Eugen Borshch Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:>=4.1 OS:Linux
Assigned to: CPU Architecture:Any
Tags: concat func, const

[21 Jun 2007 21:02] Eugen Borshch
Description:
Hello. I have the following problem: for internationalization issues I used joining  of tables using concat function (it used combination if unique identifier  and entity name as key in table for international messages). The system worked properly (using index) on MySQL 3.23 and after on MySQL 4.0.27.

After upgrading of server environment to version 5.0.42 MySQL stopped to use index for this join query become very slow accordingly. After some investigations I found that the same problem appears on MySQL 4.1.7., also I played with USE INDEX/FORCE INDEX statements without any success.

Please advice how to force MySQL 5.0.42 to use this index

How to repeat:
Queries are simplified to show the concept:

TABLES STRUCTURE:

show create table cards;

CREATE TABLE `cards` (
  `card_id` int(11) NOT NULL auto_increment,
  `short_name` varchar(5) default NULL,
  `thumb` varchar(10) NOT NULL default '',
  `large` varchar(10) NOT NULL default '',
  `card_price` float(6,2) unsigned NOT NULL default '0.00',
  `disabled` enum('YES','NO') default 'NO',
  `priority` enum('1','2','3','4','5','10') default '10',
  `callback` enum('0','1') default '0',
  `points` int(11) default '0',
  PRIMARY KEY  (`card_id`),
  KEY `card_callback_index` (`callback`)
) ENGINE=MyISAM AUTO_INCREMENT=98 DEFAULT CHARSET=latin1

show create table dynamicmessage;

CREATE TABLE `dynamicmessage` (
  `messagecode` varchar(40) NOT NULL default '',
  `languageid` int(11) NOT NULL default '1',
  `message` text,
  UNIQUE KEY `messagecode` (`messagecode`,`languageid`),
  KEY `dynamicmessage_message_index` (`message`(200))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

QUERY:
SELECT cards.card_id, message from cards left join dynamicmessage as x on (x.messagecode=concat('card_name',cards.card_id) and x.languageid=1);

EXPLAIN (MySQL 4.0.27)

explain SELECT cards.card_id, message from cards left join dynamicmessage as x on (x.messagecode=concat('card_name',cards.card_id) and x.languageid=1);
+-------+--------+---------------+-------------+---------+------------+------+-------------+
| table | type   | possible_keys | key         | key_len | ref        | rows | Extra       |
+-------+--------+---------------+-------------+---------+------------+------+-------------+
| cards | index  | NULL          | PRIMARY     |       4 | NULL       |   60 | Using index |
| x     | eq_ref | messagecode   | messagecode |      44 | func,const |    1 |             |
+-------+--------+---------------+-------------+---------+------------+------+-------------+

EXPLAIN (MySQL 5.0.42)

explain SELECT cards.card_id, message from cards left join dynamicmessage as x on (x.messagecode=concat('card_name',cards.card_id) and x.languageid=1);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | cards | index | NULL          | PRIMARY | 4       | NULL |   60 | Using index |
|  1 | SIMPLE      | x     | ALL   | messagecode   | NULL    | NULL    | NULL | 5800 |             |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

So as seen above, MySQL 4.0.27 using messagecode key for joining and 4.0.27 not using it...

Suggested fix:
Changing of query structure is not working for me as this way it will be necessary to rebuild huge capacity of code
[21 Jun 2007 21:07] Eugen Borshch
Sorry, I did misprinting:

"So as seen above, MySQL 4.0.27 using messagecode key for joining and 4.0.27 not using it..." mist be read as  "So as seen above, MySQL 4.0.27 using messagecode key for joining and  5.0.42 not using it..."
[22 Jun 2007 12:35] Martin Friebe
This *may* even be the expected behaviour? 

card_id is a number and use binary charset. This forces the whole concat to be converted to binary string.
An index can only be used for equal charsets.

The following to queries will use the index:
 explain SELECT cards.card_id, message from cards left join dynamicmessage as x on (x.messagecode=concat('card_name', convert(cards.card_id using latin1)) and x.languageid=1);
+----+-------------+-------+--------+---------------+-------------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key         | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+-------------+---------+------------+------+-------------+
|  1 | SIMPLE      | cards | index  | NULL          | PRIMARY     | 4       | NULL       |    2 | Using index |
|  1 | SIMPLE      | x     | eq_ref | messagecode   | messagecode | 46      | func,const |    1 |             |
+----+-------------+-------+--------+---------------+-------------+---------+------------+------+-------------+
2 rows in set (0.02 sec)

 explain SELECT cards.card_id, message from cards left join dynamicmessage as x on (x.messagecode=convert(concat('card_name',cards.card_id) using latin1) and x.languageid=1);
+----+-------------+-------+--------+---------------+-------------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key         | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+-------------+---------+------------+------+-------------+
|  1 | SIMPLE      | cards | index  | NULL          | PRIMARY     | 4       | NULL       |    2 | Using index |
|  1 | SIMPLE      | x     | eq_ref | messagecode   | messagecode | 46      | func,const |    1 |             |
+----+-------------+-------+--------+---------------+-------------+---------+------------+------+-------------+
2 rows in set (0.02 sec)

---
As for the issue described, http://dev.mysql.com/doc/refman/5.0/en/charset-result.html
says only:
> For operations that combine multiple string inputs and return
> a single string output, the “aggregation rules” of standard SQL
> apply for determining the collation of the result:

But it appears that a binary value will always "win".

If I understand the original request correct, this maybe a bug, or documentation + feature request
[22 Jun 2007 12:54] Eugen Borshch
Grand Thank You.
[21 Oct 2007 8:38] Valeriy Kravchuk
I think this is not a bug. Please, read http://www.mysqlperformanceblog.com/2007/10/16/be-careful-when-joining-on-concat/ for a workaround used in a similar situation.
[22 Nov 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".