MySQL 对一个表中选定的行进行计数以更新另一表中的值

Question

我创建了一个表(“文本”表)，用于存储扫描文档中的 OCR 文本。该表现在有 100,000 多条记录。它为文档中的每一页存储单独的记录。我最初设置了该表，以便它存储文档的标题及其针对每条记录的位置，这显然是糟糕的设计，因为许多记录的信息都是重复的。我随后创建了一个单独的表，现在它只为每个文档存储一条记录(“文档”表)。原始表仍然包含文档中每个页面的记录，但现在唯一的列是文档表中的 ocr 文本和文档记录的 id。

文档表有一列“total_pages”。我正在尝试使用以下查询更新此值:

UPDATE documents SET total_pages=(SELECT Count(*) from texts where texts.docs_id=documents.id)

这似乎需要很长时间才能执行，有几次我不得不崩溃。文档表中有8000多条记录。

我通过将查询限制为仅一个文档来测试该查询

UPDATE documents SET total_pages=(SELECT Count(*) from texts where texts.docs_id=documents.id and documents.id=1)

这最终只需要一条记录，但执行起来需要很长时间。我猜我的完整查询需要一些优化!非常感谢任何帮助。

Answer 1

这是您的查询:

UPDATE documents
    SET total_pages = (SELECT Count(*)
                       from texts
                       where texts.docs_id = documents.id)

为了提高性能，您需要在 texts(docs_id) 上建立索引。这可能会解决您的性能问题。事实上，可能没有必要将该值存储在主表中。

如果您决定存储计数，请确保该值保持最新。这通常需要一个触发器来处理插入和日期(如果 doc_id 更改，可能还需要更新)。