Good practice for writing subqueries in MySQL?

Question

I am writing the following helper request for some specific project purpose:

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

I was curious if this was the best practice when doing this in PHP, or should I resort to the usual way of getting the result of a subquery and then counting the records?

Answer 1

I was curious if this was the best practice when doing this in PHP, or should I resort to the usual way of getting the result of a subquery and then counting the records?

Exiting a SQL query in PHP or keeping a holy war stored procedure aside, fewer trips to the database is best practice. Time to the database and vice versa, which can never be recouped, and the separation of queries leads to the risk of data changes between queries.

? "":

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

, .

?

MySQL , MySQL SELECT .

?

MySQL SELECT SELECT. IE:

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

, , PHP, , . SQL IDE, ​​ PHPMyAdmin/etc.

Gots My Explain Plan, ?!

MySQL EXPLAIN - , .

Answer 2

, , . , , .

Answer 3

, ( ), .

Answer 4

, . . count (*), count (id)

Answer 5

It is worth noting that subqueries are only available in MySql 4.1 and higher. While ideally everyone should be on MySql 5, some users are obsessed with what their host offers (I have been stung by this several times).