MySQL script writing slow

MySQLoptimization

I'm using php and the exec() function to run 2 MySQL script files one after another

The first file has approx 2k update statements (see below) in and is 114kb in size. 

UPDATE table1 SET is_active = 1 WHERE id = 43;

The second has approx 15k update statements (see below) in and is 817kb in size.

UPDATE table2 SET is_active = 1 WHERE id = 12345;

The id column in table1 and table2 is a primary key so I would've thought this should be quite a quick update.

When I watch the MySQL Workbench dashboard for number of updates per second the first file runs at approx 80/s and the second 5k/s.

This means the smaller file with less updates is running much slower than the larger one.

Does anybody know if there is a logical reason for this?

Best Answer

I've realised that using an alternative query structure helps to speed this up.

UPDATE table1 SET is_active = 1 WHERE id IN (1,2,3,4,...)

Works much quicker and reduces the script file size to 11kb from 114kb and also reduces running time from 40 secs to just under 2 secs

Related Question