List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 24 2006 3:47am
Subject:Re: Re: Performance Question And Problem
View as plain text  
That is awesome... thanks.  I still am not sure exactly though why
this take 2 seconds while my methond took over a minute for the same
amount of rows.  In essence don't the two methods do the same things?

On 11/23/06, mos <mos99@stripped> wrote:
> At 05:50 PM 11/23/2006, you wrote:
> >I have the following query:
> >
> >      UPDATE companies c
> >      SET
> >        total_annual_service_charge =
> >          (
> >            SELECT SUM(annual_service_charge)
> >            FROM purchased_services ps WHERE ps.company_id = c.id
> >          );
> >
> >
> >It takes 1s to run when I have two tables of 500 rows, 4s with two
> >tables of 1000 rows, 15s to run with two tables to run with 2000 rows,
> >90s for two tables of 5000 rows.  This is ridiculous.  And I need to
> >run it on two tables of approx. 500,000 rows.  I need a better
> >solution.
> >
> >And there is an index on ps.company_id and c.id.  Any suggestions on
> >how I can improve my query?
> >
> >--
> >John Kopanas
> >john@stripped
>
> John,
>          I would split it into separate sql statements.
>
> 1) Create a memory table of the totals:
>
> drop table if exists CompanyTotals;
> create table CompanyTotals type=memory select Company_Id Id,
> SUM(annual_service_charge) ServCharge
>             FROM purchased_services ps group by Company_Id;
> alter table CompanyTotals add index ix_Id (Id);
>
> 2) Update the Companies table with the CompanyTotals:
> update Companies C, CompanyTotals CT set
> total_annual_service_charge=CT.ServCharge where C.Id=CT.Id;
>
> This should be much faster.
>
> Mike
>
>
>
> >http://www.kopanas.com
> >http://www.cusec.net
> >http://www.soen.info
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
John Kopanas
john@stripped

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
Thread
Performance Question And ProblemJohn Kopanas24 Nov
  • Re: Performance Question And Problemmos24 Nov
    • Re: Re: Performance Question And ProblemJohn Kopanas24 Nov
      • Re: Re: Performance Question And Problemmos24 Nov
      • Re: Re: Performance Question And ProblemBarry Newton24 Nov
  • Re: Performance Question And ProblemDan Nelson24 Nov