相关子查询在phpMyAdmin中工作，但在PHP脚本中超时

I have a query which I believe is a type of correlated subquery. If I run it as an SQL query from phpMyAdmin, it produces the expected results in about 6 seconds. The query is meant to return all the projects and the total time spent working on each project. There are about 800 rows in the projects table and 160,000 rows in the activities table.

If I run it from a PHP script as below, it doesn't work (it times out). I've got max_execution_time set to 120 seconds. This comes from a much more complicated script, in which I have all the appropriate error checking, so I have stripped it down to just the essential parts for posting here. Even with the error checking, etc in the real script, the only information I get is the fatal error "Maximum execution time of 120 seconds exceeded".

<?php

$db = new mysqli('127.0.0.1', 'userName', 'password', 'dbName');

$db->set_charset('utf8');

$query = "
  SELECT
    projects.name,
    (
      SELECT SUM(activities.effort) AS totalEffort
      FROM activities
      WHERE activities.project_ID = projects.project_ID
      GROUP BY activities.project_ID
    )
  AS totalEffort
FROM projects";

$statement = $db->prepare($query);

$statement->execute(); // time-out occurs while this line is executing

?>

Why is it timing out when run directly from the script? (And as a side point, is it possible to make this query more efficient by somehow using a join?)

No idea why it times out from php, but you can try to do it w/o mysqli (regular mysql connection) it may work. (although if it has to do with connection-specific things, memory, whatnot, it may still fail)

You can also change it to a join, I believe; something like this:

SELECT projects.project_ID, projects.name, SUM(activities.effort) AS totalEffort
FROM projects 
left outer join activities on ( activities.project_ID = projects.project_ID )
GROUP BY projects.project_ID;

Try rewriting the query as a join:

$query = "
SELECT 
    projects.name, 
    SUM(activities.effort) AS totalEffort
FROM
    projects LEFT JOIN activities ON (activities.project_ID = projects.project_ID)
GROUP BY
    activities.project_ID";

Out of interest, does the query work from the MySQL command line?

Not sure why it times out. Although your query could be rewritten. Your group by is not necessary in your select because of your where condition. This is perhaps more efficient.

SELECT projects.name, a.totalEffort
FROM projects
JOIN (
    SELECT project_id, SUM(effort) as totalEffort
    FROM activities
    GROUP BY project_id
) a ON projects.project_id = a.project_id