使用另一个的count（*）值更新大表

I have two tables. Items has the fields id,title,number. The relevant field (list) in the other table (lists) is one with comma-separated values which are IDs from items. I want items.number to be the same as the count of times the relevant ID appears in the lists table. As an example, this query is working fine to achieve this...

我有两张桌子。 Items有字段id,title,number。其他表(列表)中的相关字段(列表)是一个逗号分隔值,即来自项目的ID。我希望items.number与相关ID出现在列表中的次数相同。作为一个例子,这个查询工作正常,以实现这一目标......

update items set number = (select count(*) from lists where list like concat('%',items.id,'%')) where title > 'Z'

But if I want to update the whole of items (or even just titles beginning with 'Y', say, where there are a lot more than the mere 200 starting with Z), I get errors - either 'MySQL server has gone away' or 'Query execution was interrupted'. I assume this is because the query is too demanding. Is there a better way to achieve this without the server going splat?

但是,如果我想更新整个项目(或者甚至只是以'Y'开头的标题,比如说,从Z开始只有200多个标题),我会收到错误 - “MySQL服务器已经消失”或'查询执行被中断'。我认为这是因为查询过于苛刻。有没有更好的方法来实现这一点,没有服务器splat?

1 个解决方案

#1

Well, bad performance is the price that you pay for a poorly designed data structure. Here is your query:

嗯,糟糕的表现是你为设计不佳的数据结构付出的代价。这是您的查询:

update items
    set number = (select count(*)
                  from lists
                  where list like concat('%',items.id,'%')
                 )
     where title > 'Z';

The list like statement suggests that you are storing item ids as a list By the way, you should include the delimiter in the statement, so you don't get mismatches like 1 matching '10,11,12':

类似语句的列表表明您将项目ID存储为列表顺便说一下,您应该在语句中包含分隔符,这样就不会出现像1匹配'10,11,12'那样的不匹配:

where concat(',', list, ',') like concat('%,', items.id, ',%')

(assuming comma as a delimiter), or:

(假设逗号为分隔符),或者:

where find_in_set(items.id, list) > 0;

That said, I can't think of a way to speed up this part of the query. The right solution is to have an association table, called something like ListItems, with one row per list and item in the list. Then you could use equi-joins and indexes to speed up the query.

也就是说,我想不出一种方法来加速这部分查询。正确的解决方案是拥有一个名为ListItems的关联表,每个列表和列表中的项目有一行。然后,您可以使用equi-joins和索引来加速查询。

#1