At 09:47 PM 11/23/2006, John Kopanas wrote:
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?
The Group By executes in one operation. I may be wrong, but I think your SQL subselect has to be re-executed for each Company_Id that it finds. Joins are usually faster than a subselect and a memory table makes the joins faster.
BTW, if more than 1 person will be running this SQL at the same time, then you can use TEMPORARY table with MEMORY so each session gets their own memory table.
Glad it works. Mike (Please don't send me a service change, got enough of those already.<g>)
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]
