php - 我如何优化在嵌套循环中更新排名顺序的 mysql 查询？

Question

有一个分数链接表，其中包含一个 item_id 列表、它们被分配到的 category_id 以及一个分数。

我想做的是对于每个类别，获取分配给它的所有项目，首先按最高分排序。然后，这定义了该类别中的项目 RANK ORDER。

然后我想根据匹配的 item_id 和 category_id 将该项目 RANK 存储在同一个表中。

这适用于我的 PHP MySQL 代码，但不幸的是有 10000 个类别，因此在超时前需要大约 30 分钟。

这是我正在做的一个例子:

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

    }

}

Answer 1

尝试此查询以获取每个产品的排名(使用 session 变量，在此处查看更多信息 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

我会将这个(等级)的结果存储在一个临时表中，然后在另一个语句中通过 item_id 的简单连接更新，假设表的大小不是很大。