PHP & PDO: Fastest way to fetch data from

I have an HTML table which is fetched with thousands of rows of messages and the date these were sent by an user and I’m currently trying to understand how to fetch the data faster, as it takes 5 to 9 seconds to fully load on the page. I also want to add the fact that I’m using datatables.js for the pagination (I load 50 rows per page).

$stmt = $link->prepare("SELECT * FROM User WHERE ID = :id ORDER BY timeStamp DESC");
$stmt->bindParam(':id', $id);
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)): ?>
     <td><?php echo $row['message']; ?></td>
     ...
endwhile;

Answer

As I can see, you are just using the $row['message'] from the prepared statement. Try with the following query:

"SELECT message
FROM User
WHERE ID = :id
ORDER BY timeStamp DESC"

Also, as you said, you are using js, I guess you are using AJAX, then you should pass the current page as param and finally you can modify the query to something like:

"SELECT message
FROM User
WHERE ID = :id
ORDER BY timeStamp DESC
LIMIT :startingPosition, :recordsPerPage"

$startingPosition = currentPage times 50 (because you are displaying 50 items each page). $recordsPerPage = 50

In this way, you won’t load all records the first time you load the page (which it takes 5-9 sec as you said). Instead, it will do several queries, one for each page which is pretty faster.