posted 17 years ago
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.
MCSD, SCJP, SCWCD, SCBCD, SCJD (in progress - URLybird 1.2.1)