Let assume we have 2 tables:

table name: hids

... unique on hid

and: table name: times

... (no uniques)

The problem is: For a given user (eg. uid=00001) UPDATE status in "hids" with 0 if:

• current status is 2
• in the "times" table there isn't any record for any other user with time > (time of the latest entry for uid=00001 and the same hid)

Currently I do it with PHP is a way which is not too effecitve (thousends of atom queries). As the database grows over time (even for several milion records) the code is ineffective due to PHP overhead. Is there any option to make it simpler?

CodePudding user response：

As noted in the comments, you should make the switch to using parameterized prepared statements. Given that you are currently using sprintf to inject your variables into your SQL, it will be a very small change.

You could significantly reduce the overhead of your current process by only returning the maximum time per hid for the given user -

SELECT times.hid, MAX(times.time) AS max_time
FROM times
JOIN hids ON times.hid = hids.hid
WHERE times.uid = 1
AND hids.status = 2
GROUP BY times.hid;

But a better option is to JOIN hids and times and then use NOT EXISTS (or LEFT JOIN IS NULL) to find where there is no other user with a greater time -

UPDATE hids h
JOIN times t ON h.hid = t.hid
SET h.status = 0
WHERE t.uid = 1
AND h.status = 2
AND NOT EXISTS (
    SELECT 1 FROM times WHERE hid = t.hid AND uid <> t.uid AND time > t.time
)

Depending on the distribution of your data and how it is indexed you will probably get better performance by pre-calculating the max time per hid -

UPDATE hids h
JOIN (
    SELECT t.hid, t.uid, MAX(time) AS max_time
    FROM hids h
    JOIN times t ON h.hid = t.hid
    WHERE t.uid = 1
    AND h.status = 2
    GROUP BY t.hid, t.uid
) t ON h.hid = t.hid
SET h.status = 0
WHERE NOT EXISTS (
    SELECT 1 FROM times WHERE hid = t.hid AND uid <> t.uid AND time > t.max_time
);