How to improve query performance (using explain command results f.e.)

You can try something like this ( although its not practical for me to test this )

SELECT
    sac.surveyId,
    q.cat,
    SUM((sac.answer_id*q.weight))/SUM(q.weight) AS score,
    user.division_id,
    user.unit_id,
    user.department_id,
    user.team_id,
    division.division_name,
    unit.unit_name,
    dpt.department_name,
    team.team_name
FROM survey_answers_cache sac
    JOIN
    (
        SELECT
            s.surveyId,
            sc.subcluster_id
        FROM
            surveys s
            JOIN subcluster sc ON s.subcluster_id = sc.subcluster_id
            JOIN cluster c ON sc.cluster_id = c.cluster_id
        WHERE
            c.cluster_id=? AND sc.subcluster_id=? AND s.active=0 AND s.prepare=0
    ) AS v ON v.surveyid = sac.surveyid
    JOIN user ON user.user_id = sac.user_id
    JOIN questions q ON q.question_id = sac.question_id
    JOIN division ON division.division_id = user.division_id
    LEFT JOIN unit ON unit.unit_id = user.unit_id
    LEFT JOIN department dpt ON dpt.department_id = user.department_id
    LEFT JOIN team ON team.team_id = user.team_id
GROUP BY user.team_id, v.surveyId, q.cat
ORDER BY v.surveyId, user.team_id, q.cat ASC

So I hope I didn't mess anything up.

Anyway, the idea is in the inner query you select only the rows you need based on your where condition. This will create a smaller tmp table as it only pulls 2 fields both ints.

Then in the outer query you join to the tables that you actually pull the rest of the data from, order and group. This way you are sorting and grouping on a smaller dataset. And your where clause can run in the most optimal way.

You may even be able to omit some of these tables as your only pulling data from a few of them, but without seeing the full schema and how it's related that's hard to say.

But just generally speaking this part (The sub-query)

SELECT
    s.surveyId,
    sc.subcluster_id
FROM
    surveys s
    JOIN subcluster sc ON s.subcluster_id = sc.subcluster_id
    JOIN cluster c ON sc.cluster_id = c.cluster_id
WHERE
    c.cluster_id=? AND sc.subcluster_id=? AND s.active=0 AND s.prepare=0

Is what is directly affected by your WHERE clause. See so we can optimize this part then use it to join the rest of the data you need.

An example of removing tables can be easily deduced from the above, consider this

SELECT
    s.surveyId,
    sc.subcluster_id
FROM
    surveys s
    JOIN subcluster sc ON s.subcluster_id = sc.subcluster_id
WHERE
    sc.cluster_id=? AND sc.subcluster_id=? AND s.active=0 AND s.prepare=0

The c table cluster is never used to pull data from, only for the where. So is not

    JOIN cluster c ON sc.cluster_id = c.cluster_id
 WHERE
    c.cluster_id=?

The same as or equivalent to

WHERE
    sc.cluster_id=?

And therefore we can eliminate that join completely.

i think the problem happends when you add this:

JOIN user ON user.cluster_id = sc.subcluster_id 
JOIN survey_answers_cache sac ON (sac.surveyId = s.surveyId AND sac.user_id = user.user_id)

the extra condition sac.user_id = user.user_id can be easily not consistent.

Can you try do a second join with user table?

pd. can you add a "SHOW CREATE TABLE"