varchar join problem

MySQL & MariaDB Other MySQL® Questions
1

I’m working on a mysql server 5.0.45 hosted on linux RHEL 5.0.

  1. I’ve 2-tables as below:

table1:
acct_no VARCHAR(15)
column1 varchar(10)

table2:

acct_no VARCHAR(12)
column2 varchar(10)
In both the tables, there are many rows with identical A/c nos.

  1. Of the below 2-queries, only the second one works.

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE trim(a.acct_no) = trim(b.acct_no)

[B]
SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE a.acct_no1 = b.acct_no1[/B]


Why does varchar matching fail whereas numeric operation works?

2

Could you have different collation on the two tables?

Regardless you should never have different types/sizes of the columns part of the join condition ( and in your case you have VARCHAR(15) and VARCHAR(12) ).
The reason is that you are forcing implicit conversions(or explicit in your case with TRIM()) to happen and when performing these in a join condition you can end up in a lot of conversions which slows down the execution of the queries a lot.

3

The problem exists even if the TRIM were removed.

In fact, initially the query was written without trim. When this did not work, I tried trimming to remove problems due to whitespaces if any.

Further,I made the second table’s column width as 15 and found the problem to persist.

Both tables’ collation is: latin1_swedish_ci

I’m clueless as to why it must behave so…

4

Could you provide us with some examples of where this doesn’t work, something like the output from:

SELECT a.acct_no, b.acct_no, md5(a.acct_no) as md5a, md5(b.acct_no) as md5bFROM table1 a, table2 bWHERE a.acct_no != b.acct_nolimit 10

5

I assume he is storing numerical values such as ‘0001234’ in a varchar column. You can not easily remove trailing zeros in a string without converting the value to a number (which happens when multiplying by 1).

I don’t know why this is posted in a forum on performance.