Count Distinct Query Hangs

I am using below mentioned Query But it hangs when I try to execute the same.
Please point me out how to optimize the same query to get the result properly

I have used join because I require to use the fields in those database in where Clause but even when I remove the same then also the query does not executes but hangs

Please help me on the same

Thanks in advance


EXPLAIN SELECT count(DISTINCT FROM programs LEFT JOIN company on LEFT JOIN programtype on LEFT JOIN countries on LEFT JOIN adventureactivities on LEFT JOIN adventuredifficulty on LEFT JOIN studyprogramtype on LEFT JOIN studyfield on LEFT JOIN volunteerprojecttype on LEFT JOIN workprogramtype on LEFT JOIN languages on programs.Id=languages.programId LEFT JOIN volunteerlanguages on LEFT JOIN monthavailable on


id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE volunteerlanguages system PRIMARY 0 const row NOT found

1 SIMPLE programs ALL 247 (NULL)

1 SIMPLE company eq_ref PRIMARY PRIMARY 4 tp1.programs.companyId 1 USING index

1 SIMPLE programtype ref PRIMARY PRIMARY 4 2 USING index

1 SIMPLE countries ref PRIMARY PRIMARY 4 51 USING index

1 SIMPLE adventureactivities ref PRIMARY PRIMARY 4 1 USING index

1 SIMPLE adventuredifficulty ref PRIMARY PRIMARY 4 1 USING index

1 SIMPLE studyprogramtype ref PRIMARY PRIMARY 4 1 USING index

1 SIMPLE studyfield ref PRIMARY PRIMARY 4 1 USING index

1 SIMPLE volunteerprojecttype ref PRIMARY PRIMARY 4 6 USING index

1 SIMPLE workprogramtype ref PRIMARY PRIMARY 4 10 USING index

1 SIMPLE languages ref PRIMARY PRIMARY 4 4 USING index

1 SIMPLE monthavailable ref PRIMARY PRIMARY 4 16 USING index

on the output from EXPLAIN I do not see a large number of rows to scan.

have you tried (just for testing) omitting one at the time the JOINs until you do get a result?
I know it will not be the result you need but if you can only do one join , otherwise the server just sits there, then, it could be memory or something else.

Oh, and maybe the output from EXPLAIN … \G may be easier to read on this forum.