为新行轮询 MySQL 表的最快方法是什么? [英] What's the fastest way to poll a MySQL table for new rows?

问题描述

我的应用程序需要轮询 MySQL 数据库以获取新行.每次添加新行时，都应该检索它们.我正在考虑创建一个触发器来将新行的引用放在一个单独的表上.原始表有超过 300,000 行.

My application needs to poll a MySQL database for new rows. Every time new rows are added, they should be retrieved. I was thinking of creating a trigger to place references to new rows on a separate table. The original table has over 300,000 rows.

该应用程序是用 PHP 构建的.

The application is built in PHP.

一些好的答案，我认为这个问题值得奖励.

Some good answers, i think the question deserves a bounty.

推荐答案

对于外部应用程序，我发现使用 TimeStamp 列是一种更可靠的方法，它独立于自动 ID 和其他主键问题

For external applications I find using a TimeStamp column is a more robust method that is independent of auto id and other primary key issues

向表中添加列，例如:

insertedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP

或跟踪插入和更新

updatedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

在外部应用程序中，您需要做的就是跟踪进行民意调查时的最后一个时间戳.然后在所有相关表上从该时间戳中向前选择.在大表中，您可能需要索引时间戳列

In the external application all you need to do is track the last timestamp when you did a poll. Then select from that timestamp forward on all the relevant tables. In large tables you may need to index the timestamp column

这篇关于为新行轮询 MySQL 表的最快方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！