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
