• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

MySQL Query

 
Ranch Hand
Posts: 536
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Let nothing stop you! Not even this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic