I have a microcontroller which reads temperature from a sensor every second and serially sends it to a computer attached to it via USB. The computer has a LAMP server running on it. It takes the temperature measurements from the microcontroller and adds them to a MySQL server running on the computer. There is also a PHP file on the computer which is able to read the most recent temperature from the database and "echo" it. I have an Android app which queries the PHP file to get the temperature and update it on the screen.
Now, the question is: All of this process is happening on a local area network, so it's pretty fast. Would it be a good idea to "poll" the server every second if this was happening over the internet? How often should I ideally query the PHP file if I was doing this over the internet?
It just depends.
You can query your database as often as you can, however you may meet efficiency issue.You can just write a script which just query your database and set a clock to make that script just run for sometime (30 seconds for example). You count the total query, and output the count into file.
Use this method you can test the max qps of your database with that specific query.
Normally, you should reduce the frequency of every database query, cause the database query may be the bottleneck of your script speed.
You can implement some asynchronous method to update it. Although there is no limitation on how many times you can query but it can hamper your server performance if too many connections are open. So you have to think of some logic or time which will suit your need (get updated data to user) and also not hamper server performance. You can also lower the burden by just querying updated data not entire data (as mentioned by @Rikesh)
Create a updateTime field in your table. Each time you update the row update this field with current time. When you are querying use this field. For the first time the updateTime = 0. store the max updateTime values and use it for the next call. So that each time you are fetching rows that are updated after the given time. Another technique was cache the fetched values and make a query to fetch the data that are updated after the cache created. And sync the cached data with the updates.
If you query and fetch all data often( 1 sec as you mentioned in question), it will affect the server performance.
Use a cron job e.g set the script to run every so and so minutes...or you could use ajax to call your script every second or on events such as button clicks. If what you are aiming for is a real-time app you might want to look into some real-time technologies such as comet long-polling(facebook notifications), websockets, xmpp or as I said ajax!
If you want to show/get the only update values and reduce the time then you have to compare/check the id value with the previous one,it means
save the current retrieved id or any unique value from row(like datetime) and for the next time check the id or datetime values (like select * from table where datetime>currendatetime)so that you can get only update rows