File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
jQuery in Action, 3rd edition