List:General Discussion« Previous MessageNext Message »
From:mos Date:November 24 2006 3:01am
Subject:Re: Performance Question And Problem
View as plain text  
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
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