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:
Advertisement
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