Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
Your query is too complicated and involves too many nested loops for join proceeding. Also there are some portions of code having no effect at all. I think it can be simplified like that:
UPDATE yjob_userrank AS target
JOIN ( SELECT yjob_userrank.id AS id
, @job_id := yjob_id AS job_id
, IF(@job_id = yjob_id, @row_number := @row_number+1, 1) AS row_number
, rank_wo_date + users.rank AS combined_rank
FROM yjob_userrank AS yjur
JOIN users AS u ON u.id = yjur.user_id
WHERE yjob_id IN (29,30 ...) -- only one list required
AND @job_id = 0 -- not a NULL for correct comparison
AND @row_number = 0 -- not a NULL for correct comparison
ORDER BY yjob_id ASC
, is_match DESC
) AS source ON target.id = source.id
ORDER BY source.yjob_id ASC
, source.combined_rank DESC
SET relative_rank = source.row_number
I'm in doubt is that query is completely equivalent to your one but you can check if it produce the same set of rows if UPDATE is replaced by SELECT and final SET is commented out (just to transform a destructive query into non-destructive form). And you have to use the smaller set of yjob_ids for testing, indeed.
Something looks weird with your query. Your last ORDER BY is part of your UPDATE statement.
So I used some of your methods and simplified the query a bit. However it did not fix my deadlocks. I was not able to remove the inner select join part, as the row number get's messed up then. I Updated my questions to show the new query I created.
@OliverNybroe MySQL allows the syntax UPDATE .. ORDER BY in opposite say to the PostgreSQL. We can update rows in the certain order by dynamically calculated values of the user-defined variables.
UPDATE .. ORDER BY
Ah did not know that. However your query results in a [HY000] Incorrect usage of UPDATE and ORDER BY exception.
[HY000] Incorrect usage of UPDATE and ORDER BY
In the documentation it says For multiple-table syntax, ORDER BY and LIMIT cannot be used.
For multiple-table syntax, ORDER BY and LIMIT cannot be used.
I just made a select statement in the order by to solve that error, and it seems like it fixed everything. Do you have a better way of fixing it then an inner query in the order by?