NYCPHP Meetup

[Jeffrey Knight]

I take it that Vtems.MCN and feed.mcn are indexed?

Jeff
New York, NY

On 1/6/06, Joseph Crawford <codebowl at gmail.com> wrote:
> 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
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>
>