I'm writing the next sub query for many project specific purpose:

SELECT count(*) from table1 
WHERE userB='$p' AND userA IN 
  (SELECT userB FROM table1 
   WHERE userA='$row[username]')

I had been curious if the was the very best practice when you are performing it in PHP or must i lean towards the conventional method of first obtaining the subquery result after which counting the records?

I had been curious if the was the very best practice when you are performing it in PHP or must i lean towards the conventional method of first obtaining the subquery result after which counting the records?

Departing the SQL query in PHP or saved procedure holy war aside, less outings towards the database is the greatest practice. There's time for you to the database &lifier back that may not be recovered, and separating the queries runs the chance of data altering among the queries.

Can the query itself be enhanced? Within this example, potentially yes:

SELECT COUNT(*) 
  FROM TABLE t
  JOIN TABLE t2 ON t2.userB = t.userA
               AND t2.userA = '$row[username]'
 WHERE t.userB = '$p'

If you want to be certain about query performance, you will have to learn producing an explain plan &lifier interpretation the output to tune the query.

What's an Explain Plan?

The MySQL explain plan shows the way the MySQL query optimizer has made the decision to operate a Choose statement to be able to best access the information which has been asked for.

How Do You Generate an Explain Plan?

In MySQL, you just need to add the keyword explain towards the Choose query, prior to the SELECT keyword. IE:

EXPLAIN SELECT COUNT(*)
          FROM TABLE t
          JOIN TABLE t2 ON t2.userB = t.userA
                       AND t2.userA = '$row[username]'
         WHERE t.userB = '$p'

Most likely must have pointed out this earlier, but you won't want to run this from inside PHP since it will not return that which you queried for. Go to whichever SQL IDE, like PHPMyAdmin/etc.

I have Gots My Explain Plan, But Exactly What Does it Mean?!

The MySQL EXPLAIN documentation is a great spot to educate yourself on each column that's came back, &lifier exactly what the column signifies.

As lengthy because the subquery does not result in a performance hit, I believe it's easier to stay with one query. It'll minimize the code you need to write and keep, that is more often than not a positive thing.

Generally, most databases perform better with left joins (which your query might be transformed into) than subqueries.

To be sure with Ted, left join is going to be faster. and it is simpler to see too. also dont use count(*), use count(id)

Worth observing that subqueries are just obtainable in MySql 4.1 and greater. While ideally everybody ought to be on MySql 5, some customers are tied to what their host offers (I've been stung with this a couple of occasions).