0

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?

5
  • I think MySQL is the same as SQL Server in the respect that using a function on a value in a join will make it non-sargable. If it is unable to leverage an index, that could be causing the slowness.
    – dfundako
    Commented Aug 15, 2016 at 16:06
  • @dfundako you're right, but LIKE is super slow.
    – Googlebot
    Commented Aug 15, 2016 at 16:08
  • create a temp table and insert everything into it from table2 and also make a new column that already has the %. appended to col2. Index that column then run your update with the temp table instead of table2.
    – dfundako
    Commented Aug 15, 2016 at 16:10
  • Do you have an index on a starting with col1? (That should be sargable.)
    – Rick James
    Commented Aug 16, 2016 at 0:53
  • The real answer is design the schema so that you do not to need to use expressions in ON.
    – Rick James
    Commented Aug 16, 2016 at 0:54

1 Answer 1

3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.