Mysql – How to JOIN two tables with substring

join;MySQLupdate

I update JOINed tables as

UPDATE table1 a 
JOIN table2 b 
ON a.col1 LIKE CONCAT('%', '.', b.col2)
SET a.foreign_id=b.foreign_id

but LIKE is very slow for JOIN. Is there another way based on faster substring functions to make the JOIN?

Best Answer

The leading wildcard in the like, not the function, is preventing the use of the table1.col1 index (assuming it is indexed).

For an indexed approach, the values can be store reversed...

update table1 set col1=reverse(col1);
alter table1 add key (col1);  -- if not already indexed

update table2 set col2=reverse(col2);

update table1 a
join table2 b    on a.col1 like concat(b.col2,'.%')
set a.foreign_id=b.foreign_id;

Of course, this comes with the downside that the column will need to be reversed for display...

select reverse(col1) from table1 where...

Here is a similar denormalized approach with the reversed value redundantly stored in the same table...

alter table table1 add column col1_rev -- col1 datatype specification here;
update table1 set col1_rev=reverse(col1);
alter table table1 add key (col1_rev);

alter table table2 add column col2_rev -- col2 datatype specification here;
update table2 set col2_rev=reverse(col2);
alter table table2 add key (col2_rev);

update table1 a
  join table2 b on a.col1_rev like concat(b.col2_rev,'.%')
    set a.foreign_id=b.foreign_id;

A normalized approach would be another table of unique table1.col1 values as stored and reversed...

create table table1_col1_rev as
select distinct col1
     , reverse(col1) as col1_rev
  from table1;

alter table table1_col1_rev
  add key (col1)
        , (col1_rev);

update table1 a
     , table1_col1_rev b
     , table2 c
   set a.foreign_id=c.foreign_id
 where b.col1_rev like reverse(concat('%.',c.col2))
   and a.col1=b.col1;
Related Question