• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL Query

 
Richard Green
Ranch Hand
Posts: 536
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have two tables


table1
------------

idnamefound
----------------------------------------------
1yy1No
2yy2No
3yy3No

table2
---------

id text
-------------------------------------------------------
1something yy1 yy3 blah blah
2 yeah right yy3


I want a sql script to basically go through the names in table1, and see if they are present in table2 and if so, update the "found" field to "Yes"


So, I wrote this

UPDATE table1
SET found = 'Yes'
WHERE exists
( SELECT 1 from table2
where text like concat(concat('%',table1.name),'%'))
;


It worked perfectly in a dev environment (where I had 100 records in table1 and 2), but in prod we have about 2 million records and MySQL just hangs when I run this query.

Any ideas on how to fix this?

Oh btw table1.name and table2.text are text fields - I indexed these two fields but that didnt make much difference.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic