How to update rows with new values for groups found and make this as fast as posible?

I have a table which have these fields: id, client_id, visit_id, number_of_visit.

This table can have from 30k to 100+k rows of data. The primary key is client_id and visit_id. (I cannot have a row with these two values being the same, but meanwhile client_id can appear more than 1 time having different visit_ids and so on).

The thing I want to do is to write values to the field number_of_visit, and this value should be a sequence number of this visit (the first, second, third and so on). I have the visit_id field which is actually a large number, and I need to order it as ‘ASC’ and write values to number_of_visit accordingly. I have this:

id   client_id   visit_id   number_of_visit
1    103         10         0
2    103         11         0
3    104         12         0
4    105         13         0
5    105         15         0
6    105         16         0

And I want to do this:

id   client_id   visit_id   number_of_visit
1    103         10         1
2    103         11         2
3    104         12         1
4    105         13         1
5    105         15         2
6    105         16         3

I write values to number_of_visit from 1 to n in the same order as visit_id values in ASC order.

This table is able to have more than 1 row with the same client_id, but cannot have a row with the same visit_id, because one visit_id is always relative to a particular client_id. I need to get all the visit_id‘s of all client_id‘s in this table, and update the number_of_visit field of these rows with numbers 1, 2, 3, which represent sequence numbers. (if this is the first visit, or it’s the second, third and so on …).

I have this PHP code:

$visit_ids = [];

$sql = "SELECT clientID, GROUP_CONCAT(id) as 'visit_ids' FROM table_report GROUP BY clientID";
$stmt = $pdo->prepare($sql);
$stmt->execute();

while ($row = $stmt->fetch()) {

    $visit_ids = explode(',', $row['visit_ids']);

    $counter = 1;
    foreach ($visit_ids as $id) 
    {
        $query = "UPDATE table_report SET number_of_visit = :value WHERE id = :id";
        $statement = $pdo->prepare($query);
        $statement->execute([':value' => $counter, ":id" => $id]);
        $counter++;
    }
}

But the main problem is that it’s too slow. The table contains about 40k rows now, but could have more than 100k+. When it’s 40k it takes about an hour or two to complete.

What can I do to make this more faster? I have indexes on these fields.

Here’s also EXPLAIN output:

Answer

Use row_number():

select t.*, row_number() over(partition by client_id order by visit_id) number_of_visit
from table_report t

If you wanted an update statement:

update table_report t
inner join (
    select id, row_number() over(partition by client_id order by visit_id) number_of_visit
    from table_report t1
) t1 on t1.id = t.id
set t.number_of_visit = t1.number_of_visit