I am facing a situation where I have to fetch and show around 200,000 rows from a MySql table to html. Now the issue is it is taking a lot of time if I fetch all rows and send it to client via ajax.
But I am thinking to fetch data in streaming mode from MySql table and also send that to client in streaming mode.
Is it possible at all to fetch and send data in streaming mode?
If it is can you please give me a start point from where to start?
Consider my ajax as;
$.ajax({
url:"myurl.com",
method:"post",
data:jsonData,
success:function(data){
console.log(data);
}
})
And the php is like;
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$result=$conn->query("select * from tableName");
$ret=Array();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
array_push($ret,$row);
}
}
else {
echo "0 results";
}
echo json_encode($ret);
Upto this I have done, but what are the changes are necessary to do the stuffs in streaming mode.
If your intend is to send multiple requests over a single connection, then you need to look into HTTP/2 or HTTP/3. These allow for one connection to be used back and forth i.e. the client connects and you get a two way connection where you can send and receive any number of requests. More specifically, you can send one request to register with your server then your server can send as much data as it wants forever. This is how most modern websites are implemented. Those where you change one thing in one browser and it auto-updates in another... that's HTTP/2 (TCP) or HTTP/3 (UDP).
With HTTP/1.1, you need to use one request with all the queries you need and then your server can send all the replies one after the other. Say you are replying with JSON data, you could write one JSON object per line. If you want to use the streaming feature of HTTP/1.1, it's doable, but not very useful unless the reply is fairly large (like over 64Kb).