Page 1 of 1

Can this be made more efficient?

Posted: Fri Apr 20, 2007 6:25 pm
by voltrader
I have an old database that I'd like to update with new information.

-- I would like to compare 2 fields between two tables with about 90k rows each.

-- based on those comparisons, I'd like to UPDATE a field in one of the tables

Currently, I'm using this query, which has taken 1 hour already:

Code: Select all

UPDATE maintable, backuptable SET maintable.refnumber = backuptable.refnumber WHERE maintable.name=backuptable.name AND maintable.city=backuptable.city AND maintable.country=backuptable.country AND maintable.refnumber=0
Is there something more efficient I can do to cut down on the time it takes?

Posted: Fri Apr 20, 2007 6:36 pm
by nickvd
I'm not an sql guru, especially when it comes to optimizing queries, but you could always retrieve the data you want and do the processing in php...

Re: Can this be made more efficient?

Posted: Tue Apr 24, 2007 9:34 am
by printf
voltrader wrote:Is there something more efficient I can do to cut down on the time it takes?
No way to know without seeing your table scheme(s) for (maintable, backuptable), but if it's taking an hour then your join is most likely not optimized, (indexing problem)

Posted: Tue Apr 24, 2007 10:09 am
by mpeacock
I'd go with printf's suggestion. Index the columns in both tables that you're joining on - name, city, country, and refnumber.

One way to possibly speed this would be to reduce the number of fields you're joining on. If the main and backup tables share the same PK - simply join on that.

Nickvd's idea is also good - especially if the percentage of rows you're updating is relatively low. The query you have will examine each of the 90K rows in each table and update as needed. It might be faster to write a script that:

Finds all the rows in maintable that has a refnumber = 0
foreach row
{
find the matching row in backup table
update the refnumber in the maintable with the backup table value
}

Either way - check your indexes.

Cheers, Michael