Data locking prevents query from running - MySQL InnoDB

We need to run a script every 24h that calculates sums & avarages of data from 1 big log table and updates the data in the Main_Table (Innodb) that has many select/update queries all the time (about 1 per second)

I ran a test query on a local DB which took about 10 minutes:

Update Main_Table Set Steps = (Select count(*) From Log_Activity Where Log_Activity.MainID = Main_Table.ID)

And because this updates all the rows in the Main_Table everything get stuck in the live project because of table loacking I guess - I had to kill the process of the query in order for this to start working properly again - no data was updated.

I did some digging and I'm considering the following 3 options:

Update all data in a temp table and then merge into the Main_Table somehow.
Run the script in php loops instead of 1 MySql query (will take much more time, but update 1 row at a time).
I'm not sure if this has to do with our problem but I read something about "START TRANSACTION... etc'" and I think this might be related... is it?

Hope someone can shed some light on this and help us. Thanks

Solution

Create an index on Log_Activity.MainID, if one does not already exist:
```
ALTER TABLE Log_Activity ADD INDEX (MainID);
```
Create an index on Main_Table.ID, if one does not already exist:
```
ALTER TABLE Main_Table ADD INDEX (ID);
```

Rewrite your query to use a join, rather than a correlated subquery:

UPDATE Main_Table JOIN (
  SELECT   MainID ID, COUNT(*) c
  FROM     Log_Activity
  GROUP BY MainID
) t USING (ID)
SET Main_Table.Steps = t.c;