NYCPHP Meetup

[Joseph Crawford]

Hello guys,

I have a sql query that basically copies data from one table to another,
here is the query

use mydb;
update Vtems left join  feed on Vtems.MCN=feed.mcn
set VItems.Inventory= case (VItems.V_ID)
when 'CN3' then feed.cn3quan
when 'CN4' then feed.cn4quan
when 'CN5' then feed.cn5quan
when 'CN6' then feed.cn6quan
when 'CN7' then feed.cn7quan
when 'CN8' then feed.cn8quan
when 'CN9' then feed.cn9quan
when 'CN10' then feed.cn10quan
when 'CN11' then feed.cn11quan
when 'CN16' then feed.cn16quan
else '' end
,VItems.ItemCst = feed.unitcost,
VItems.ItemPrce = feed.unitcost
commit;


We have a php cron job which executes and runs this query, however it takes
anywhere between 4-8 hours for this to run on 50,000 records, now this
number will also grow and we have determined that the left join is what is
slowing it down.  What i need to know is do you have any suggestions on a
better method for doing this that would speed it up considerably.

I know we could run this on the mysql command line and we have tried.  It
seems to take just as long on the command line as the php cron, however we
are also wishing to implement emails when there are errors etc..  The avg
time is 6 hours on the production server to complete the import.

Thanks,
--
Joseph Crawford Jr.
Zend Certified Engineer
Codebowl Solutions, Inc.
1-802-671-2021
codebowl at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20060106/d7e9f23e/attachment.html>