In the last episode (Nov 23), John Kopanas said:
> 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?
If you're I/O bound during this query, try an index on
(company_id,annual_service_charge) on your purchased_services table.
That'll let the subquery complete using just an index scan. If that
doesn't help, try mos's idea.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
