Slow Query - Help with Optimization

Question

Hey guys. This is a follow-on from this question:

After getting the right data and making some tweaks based on requests from business, I've now got this mini-beast on my hands. This query should return the total number of new jobseeker registrations and the number of new uploaded CV's:

SELECT COUNT(j.jobseeker_id) as new_registrations,
(
    SELECT 
      COUNT(c.cv_id)
    FROM 
      tb_cv as c, tb_jobseeker, tb_industry
    WHERE
      UNIX_TIMESTAMP(c.created_at) >= '1241125200'
    AND 
      UNIX_TIMESTAMP(c.created_at) <= '1243717200'
    AND 
      tb_jobseeker.industry_id = tb_industry.industry_id
) 
AS uploaded_cvs
FROM 
  tb_jobseeker as j, tb_industry as i
WHERE
  j.created_at BETWEEN '2009-05-01' AND '2009-05-31'
AND
  i.industry_id = j.industry_id
GROUP BY i.description, MONTH(j.created_at)

Notes: - The two values in the UNIX TIMESTAMP functions are passed in as parameters from the report module in our backend.

Every time I run it, MySQL chokes and lingers silently into the ether of the Interweb.

Help is appreciated.

Update: Hey guys. Thanks a lot for all the thoughtful and helpful comments. I'm only 2 weeks into my role here, so I'm still learning the schema. So, this query is somewhere between a thumbsuck and an educated guess. Will start to answer all your questions now.

Answer 1

First and foremost it may be worth moving the 'UNIX_TIMESTAMP' conversions to the other side of the equation (that is, perform a reverse function on the literal timestamp values at the other side of the >= and <=). That'll avoid the inner query having to perform the conversions for every record, rather than once for the query.

Also, why does the uploaded_cvs query not have any where clause linking it to the outer query? Am I missing something here?