Is it a good practice to write subqueries in MySQL?

Question

I am writing the following sub query for some project specific 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 it in PHP or should I resort to the conventional way of first getting the subquery result and then counting the records?

Answer 1

As long as the subquery doesn't cause a performance hit, I think it's better to stick to one query. It will minimize the code you have to write and maintain, which is almost always a good thing.

Answer 2

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

Leaving the SQL query in PHP or stored procedure holy war aside, less trips to the database is the best practice. There's time to the database & back that can never be recouped, and separating the queries runs the risk of data changing in between the queries.

Can the query itself be optimized? In 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 really want to be sure about query performance, you'll have to get familiar with generating an explain plan & interpreting the output to tune the query.

What's an Explain Plan?

The MySQL explain plan shows how the MySQL query optimizer has decided to run a SELECT statement in order to best access the data that's been requested.

How Do I Generate an Explain Plan?

In MySQL, you just have to add the keyword explain to the SELECT query, before 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'

Probably should have mentioned this earlier, but you don't want to run this from within PHP because it won't return what you queried for. Use whatever SQL IDE, like PHPMyAdmin/etc.

I've Gots My Explain Plan, But What Does it Mean?!

The MySQL EXPLAIN documentation is a good place to read up on each column that is returned, & what the column represents.

Answer 3

In general, most databases perform better with left joins (which your query could be converted to) than subqueries.

Answer 4

I agree with Ted, left join will be faster. and its easier to read too. also dont use count(*), use count(id)

Answer 5

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