aspose file tools*
The moose likes JDBC 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
Bookmark "MySQL Query" Watch "MySQL Query" New topic
Author

MySQL Query

Richard Green
Ranch Hand

Joined: Aug 25, 2005
Posts: 536
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)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL Query
 
Similar Threads
Help with a query?
Hibernate: Different Join in Save() and Get()
Any one have idea regarding this query??
Query help
Projection from inner join