MySQL: simplifying a complex query

Question

I made the following query based on many tutorials from blogs and answers on stackoverflow.

$query = mysql_query("SELECT COUNT(user_id) FROM $table f1".
"WHERE (user_id = '$userA')".
"AND EXISTS (SELECT 1 FROM $table f2 WHERE user_id = '$userB'"
"AND f1.$field = f2.$field)")  

or die(mysql_error());  
while($row = mysql_fetch_array($query)){
    $com = $row['COUNT(user_id)'];
}

The parameters such as userA, userB are taken by an one-dimension array of 4000 values. But actually, i use loop inside the loop to make a table of 4000x4000 size.

Due to many data in the database, the total queries will be 17.000.000. So, i gave a try to execute the full script but i saw that at the above query took many seconds (1 or 2).

Do you have any suggestion to increase the execution speed by correcting the above query?

Answer 1

I think you can use a INNER JOIN query as below:

 SELECT COUNT(f1.user_id) 
 FROM $table f1 JOIN $table f2 
    ON f1.$field = f2.$field
 WHERE f1.user_id = '$userA'
    AND f2.user_id = '$userB'