1 comments

@Kondybas 2019-04-15 14:43:12

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.

@Oliver Nybroe 2019-04-15 15:43:39

Something looks weird with your query. Your last ORDER BY is part of your UPDATE statement.

@Oliver Nybroe 2019-04-15 15:58:22

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.

@Kondybas 2019-04-15 16:48:21

@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.

@Oliver Nybroe 2019-04-16 07:57:14

Ah did not know that. However your query results in a [HY000][1221] Incorrect usage of UPDATE and ORDER BY exception.

@Oliver Nybroe 2019-04-16 08:22:10

In the documentation it says For multiple-table syntax, ORDER BY and LIMIT cannot be used.

@Oliver Nybroe 2019-04-16 08:56:01

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?

Related Questions

Sponsored Content

2 Answered Questions

How to improve query count execution with mySql replicate?

1 Answered Questions

Deadlocks appeared after moving to MariaDB10.2 from MySQL5.5

2 Answered Questions

Mysql join not working

  • 2015-01-28 00:42:40
  • Rob D
  • 103 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql join

5 Answered Questions

1 Answered Questions

[SOLVED] Replace Subquery with JOIN - MYSQL

2 Answered Questions

[SOLVED] select MAX() from MySQL view (2x INNER JOIN) is slow

  • 2018-02-14 22:34:15
  • matt
  • 416 View
  • 3 Score
  • 2 Answer
  • Tags:   mysql join view max

2 Answered Questions

[SOLVED] #1093 - You can't specify target table 'R' for update in FROM clause

1 Answered Questions

[SOLVED] Need to find the employees who have switched jobs atleast twice

3 Answered Questions

[SOLVED] Inserting and updating and selecting at thousands of times per second

1 Answered Questions

[SOLVED] Unexplained InnoDB timeouts

Sponsored Content