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 <[EMAIL PROTECTED]> 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
>[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
John Kopanas
[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
