Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes JDBC and Relational Databases and the fly likes MySQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MySQL Query" Watch "MySQL Query" New topic

MySQL Query

Richard Green
Ranch Hand

Joined: Aug 25, 2005
Posts: 536
i have two tables




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('%',,'%'))

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 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)
I agree. Here's the link:
subject: MySQL Query
It's not a secret anymore!