[SOLVED] Repeated MySQL fetch results in high resources usage on PHP

Repeated MySQL fetch results in high resources usage on PHP

In my PHP page I used repeated checks for new updates/insert using Ajax that results in high resource usage. Some code I used for notification as follows:

$sql = "SELECT * FROM Notification WHERE user_id='$_GET[user_id]'";
  $count=mysqli_query($conn, $sql);  
  $cnt=mysqli_query($conn, $count);                                   
  $ct=mysqli_num_rows($cnt);

Similar MySQL fetch used in chatting page results in server error (more than 50 users sending messages and inserting data to database so lots of rows, moreover app on shared hosting). How can I avoid regular checks and get data when the new data is inserted or updated in the database?

Solution

You don't say how much data is in your table. There are several possible reasons for the problem

1) Lots of records returned from this query - quite possible for active users 2) You don't have an index on the user_id column, which means expensive queries

You probably need to ask only for data since the last time you asked, which will be more efficient