PHP Instant Message API

Question

I am creating a chat API for Android developers, but it's slow -- the chat app is getting messages a little bit late.

How can I improve the performance of my code?

<?php
require_once('config.php');
$sender_id = $_GET['sender_id'];
$receive_id = $_GET['receive_id'];
$response = array();
if($sender_id && $receive_id)
{
 $results = mysql_query("select * from cpo_chat_system where (sender_id = '$sender_id' AND receive_id = '$receive_id') union select * from cpo_chat_system where (sender_id = '$receive_id' AND receive_id = '$sender_id') ORDER BY date ASC, time ASC");
 $total_record = mysql_num_rows($results); 
 if($total_record > 0)
 {
 $response['success'] = '1';
 while($row = mysql_fetch_assoc($results))
 { 
 $response['user'][] = $row; 
 }
 }
 else
 {
 $response['success'] = '0';
 $response['user']['error'] = "1";
 $response['user']['error_msg'] = "Not Data Found";
 }
}
echo json_encode($response);
?>

• bumperbox: Have you profiled it to see where it is slow. Looking at the code I would guess it is in the database query. Once you have confirmed what is slow, let us know, and then we can help you some more.
• Jeroen: I would really recommend escaping your $_GET strings and changing to mysqli instead of mysql (mysql is getting depricated!). $sender_id = mysql_real_escape_string($_GET['sender_id']; You can read more about the deprication of mysql here

Answer 1

Before worrying about performance, you should worry about security. You are vulnerable to SQL injection. Never put any variables directly into queries, use prepared statements instead.

Other than that:

• mysql_ is deprecated for quite a while, and it will be removed in future PHP versions. You really shouldn't write code using it, use PDO or mysqli_ instead (and as said, with prepared statements).
• upper-case all SQL keywords in your queries to improve readability.
• your indentation is inconsistent.
• your query seems a bit off. Do you really need union? Shouldn't something like WHERE (sender_id = '$sender_id' AND receive_id = '$receive_id') OR (sender_id = '$receive_id' AND receive_id = '$sender_id') work?
• I would change the date and time columns to a datetime column, it should speed up your query a bit.
• receive should be receiver to fit in with sender.

• Azad Chouhan: thank you for your answer I will take care of all the points..