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]
