I am updating the script to a new version with a new database layout. The update will start normally, but it slowly starts to take more and more time for the same request. The question asked is as follows:
SELECT nuser.user_id, nfriend.user_id AS friend_user_id, f.time
FROM oldtable_friends AS f
JOIN oldtable_user AS u ON ( u.user = f.user )
JOIN newtable_user AS nuser ON ( nuser.upgrade_user_id = u.id )
JOIN oldtable_user AS uf ON ( uf.user = f.friend )
JOIN newtable_user AS nfriend ON ( nfriend.upgrade_user_id = uf.id )
LIMIT 200
OFFSET 355600
The OFFSET here, of course, is changing as data is retrieved in batches of 200 records.
oldtable_friends has about 2 million records.
oldtable_user and newtable_user have about 70,000 entries.
This query is executed very quickly at first, but slowly begins to take shape, and after a couple of hours it takes about 30 seconds. These tables do not change at all while the script is being updated, so I'm not sure where this bottleneck is. The query appears to slow down as the OFFSET variable increases.
Here is the EXPLAIN:
+----+-------------+---------+--------+-----------------+-----------------+---------+-----------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+-----------------+-----------------+---------+-----------------------------------+-------+-------------+
| 1 | SIMPLE | nuser | ALL | upgrade_user_id | NULL | NULL | NULL | 71638 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY,user | PRIMARY | 4 | database.nuser.upgrade_user_id | 1 | |
| 1 | SIMPLE | f | ref | user,friend | user | 77 | database.u.user | 20 | |
| 1 | SIMPLE | uf | eq_ref | PRIMARY,user | user | 77 | database.f.friend | 1 | |
| 1 | SIMPLE | nfriend | ref | upgrade_user_id | upgrade_user_id | 5 | database.uf.id | 1 | Using where |
+----+-------------+---------+--------+-----------------+-----------------+---------+-----------------------------------+-------+-------------+
. . MySQL, , . ?