How to make a mysql query constantly refresh in PHP?

I’m making a basic chat room. My code:

$conn = ("127.0.0.1","root","","mymessages");

$stmt = "SELECT * FROM posts ORDER BY timestamp LIMIT 100";
$result = mysqli_query($conn, $stmt);

if(!$result) {
    echo("Query failed" . mysqli_error());
}

while ($row = mysqli_fetch_assoc($result)) {
    $messagefname = $row['fname'];
    $messagelname = $row['lname'];
    $subject = $row['subject'];
    $content = $row['content'];
    $day = $row['day'];
    $month = $row['month'];
    $year = $row['year'];
    $hour = $row['hour'];
    $min = $row['minute'];


    echo("<font size='1.5' face='Arial'>");
    echo("Sent by " . $messagefname . " " . $messagelname . " at " . $hour . ":" . $min. " on " . $day . "/" . $month . "/" . $year . "<br>");
    echo("</font><br><font size='4' face='Arial'><b>");
    echo($subject);
    echo("</font><br><font size='3' face='Arial'></b>");
    echo($content);
    echo("</font><br><br><hr><br>");
}

Every time you refresh the page, this updates, and any records that have been added since the last refresh are added to the list. In a chat room, this isn’t ideal, so is there a way to have this constantly checking for new records with as little disruption to the user as possible? Thanks!

Answer

You’re stuck with polling with your current tech stack. MySQL doesn’t have any good way to push a notification to you that there’s something new to see.

(You could consider adding a message queuing subsystem, like RabbitMQ for example, but that would require a big change to the structure of your application).

Some guidelines for handling polling at the least possible cost.

1. Set user expectations at for a few seconds’ latency on the messages rather than milliseconds.

2. Query as often as necessary to meet the expectations for latency.

3. Avoid querying lots of data with each query. How can you do that?

   a. Save the most recent timestamp whenever you run a query

   b. Don’t use SELECT *. Instead give the names of the columns you actually need. This lets MySQL’s optimizer help cut the cost of your queries.

   c. Make your queries do SELECT whatever WHERE timestamp > saved_timestamp ORDER BY timestamp so you only get new items from your table, in order. If your system is not very busy, these SELECTs will often return no rows. That’s good.

   d. Make sure you have a multicolumn index on timestamp and the other columns in your SELECT statement. This is called a covering index.

   e. Right after you open your connection to MySQL, issue this SQL statement. It lets MySQL fetch your data with less contention with other MySQL clients inserting rows to to the table.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

If you’re doing this with AJAX from your users’ browsers, you want to include the latest timestamp in the AJAX requests so you can deliver new the needed new items to each user’s browser.