SELECT JOIN with LIKE issue

dmacman1962

Hi All,

I am trying to do a JOIN where the EmailAddress in one table is LIKE the one in the first table. Reason is some of the addresses where concatenated (not by me) and I want to test for a match.

My challenge is how to use the LIKE '%something%' where the something it the field name in the one table. As such:

SELECT max8.maxID, houselist.HLEmailAddress AS HLE, max8.EmailAddress, houselist.Processed
FROM houselist, max8
WHERE max8.EmailAddress LIKE '%HLE%' AND houselist.Processed='0'

Note the max8 table has 1.8 million records, and the houselist has 160,000.

What my final goal it to lookup the email addresses in the max8 table from houselist, then get their max8.maxID then update the houselist with that ID and mark them as processed and mark the max8 table with a modified date.

My issue is I got this to work with individual queries, (with a small sample houselist) but it times out (set to 60 seconds) before it completes the large list.

Does that make sense?

I appreciate the help,

regards,
Don

Weedpacket

What's timing out? I'm guessing this will be a one-off maintenance kind of query to tidy up the data, so how long it takes wouldn't really be that much of an issue (unlike something that's needed in realtime). On big databases I often see massive hairy updates (you say update but you write a select) take several minutes.

dmacman1962

What is timing out is the select for the large database. I have the "max_execution_time = 60 ".

And you are correct, this is a one-time database cleanup. I probably could bump this way up, and run it in an 'off' time. Right now, we are getting a lot of teachers (the email addresses in the database) coming to the site. The traffic is relatively slow over the weekends (when they are not in school). I could try it then.

When I say update, I mean updating the houselist and max8 databases after matching the records in the select query. Here are the 2 updates.

$sql = "UPDATE houselist ";
$sql .= "SET maxID='$maxID', Processed='1', Date='$Date' WHERE HLEmailAddress='$EmailAddress'";

$sql = "UPDATE max8 ";
$sql .= "SET BecameCustomer='1', Modified='$Date' WHERE maxID='$maxID'";

After this 'clean up' I could turn it back down and future clean ups would be very small lists.

Does that help?

Thanks,
Don

Weedpacket

Could you not just run the query directly on the database? Or is via PHP your only access? (set_time_limit(0) will remove PHP's timeout limitation.)

Is it also possible to, instead of running one query to get these IDs and then putting them into what looks like it could be a lot of update statements, putting the search into the update statement as well and do it in one command? If yes, then that would save a lot of time right there (if you haven't got past the select yet you'll be surprised how long all those updates might take).