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: | |
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
[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".