How do i optimise a mysql query that updates rank order in nested loop?

Question

There is a score link table that holds a list of item_ids, the category_ids they are assigned to, and a score.

What I am trying to do is For each category, grab all the items assigned to it, ordered by highest score first. This then defines that items RANK ORDER in that category.

I then want to store that items RANK in the same table against the matching item_id and category_id.

This works great with the PHP MySQL code i have, but unfortunately there are 10000 categories and so takes about 30mins before timing out.

Here is an example of what im doing:

SELECT category_id FROM tbl_categories  /* - this just grabs a list of categorys to loop through */

Loop through each row returned {

    SELECT item_id, score 
    FROM tbl_scores 
    WHERE category_id = CATEGORY_ID ORDER BY score DESC
    MYCOUNT = 0

    Loop through each results, incrementing the count and storing in another tables {

      MYCOUNT = MYCOUNT + 1
      UPDATE tbl_scores 
      SET rank = MYCOUNT 
      WHERE item_id = ITEMID AND category_id = CATEGORYID

    }

}

Look into using session variables as counters , as explained here http://stackoverflow.com/questions/2728413/equivalent-of-oracle-s-rowid-in-mysql I think you can achieve what you wanted with a single join statement and counter variable. — Yossi Vainshtein, Feb 05 '17 at 12:51
I assumed it could be done with a single join and counter. I did look into using the counters as row_number is not available in MySQL but not sure how to create the singe update statement. I would appreciated an example of how to go about this as my head has started to turn to mush :) — Shaun, Feb 05 '17 at 12:58
can you give us sample data in http://sqlfiddle.com/ and the output that you want — Bernd Buffen, Feb 05 '17 at 13:57
Here is the sql fiddle for the initial example result set http://sqlfiddle.com/#!9/ca9a95 - the script i have would pull out all the items for a specific category, ordered by score - then would loop through using a counter to set the rank column - the output i would expect is here : http://sqlfiddle.com/#!9/39f49a/1 — Shaun, Feb 05 '17 at 21:59

score 0 · Accepted Answer · edited May 23 '17 at 12:01

Try this query to get the rank for each product (using session variables, see more info here MySql - How get value in previous row and value in next row?)

SELECT          
    s.categoryid    ,       
    s.itemid,       
    s.score,        
    @rowid := IF (@prev_categroy = s.categoryid, @rowid+1, 0),
    @prev_categroy := s.categoryid AS curr  
FROM scores s, (SELECT @rowid:=0, @prev_categroy := NULL) AS init   
ORDER BY s.categoryid, s.score DESC

I would store the results of this (ranks) in a temporary table and then update in another statement with a simple join by item_id, supposing the size of the table is not huge.

That was amazingly helpful, and worked great. Thanks a lot. – Shaun Feb 06 '17 at 10:28 — Shaun, Feb 06 '17 at 10:28

How do i optimise a mysql query that updates rank order in nested loop?

1 Answers1