Can this be made more efficient?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Can this be made more efficient?

Post 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?
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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...
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Re: Can this be made more efficient?

Post 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)
User avatar
mpeacock
Forum Newbie
Posts: 10
Joined: Thu Apr 12, 2007 9:07 am
Location: Mobile AL

Post 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
Post Reply