Count Distinct Query Hangs

MySQL & MariaDB Other MySQL® Questions
1

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

CSJakharia

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

Resultset

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 tp1.programs.id 2 USING index

1 SIMPLE countries ref PRIMARY PRIMARY 4 tp1.programs.id 51 USING index

1 SIMPLE adventureactivities ref PRIMARY PRIMARY 4 tp1.programs.id 1 USING index

1 SIMPLE adventuredifficulty ref PRIMARY PRIMARY 4 tp1.programs.id 1 USING index

1 SIMPLE studyprogramtype ref PRIMARY PRIMARY 4 tp1.programs.id 1 USING index

1 SIMPLE studyfield ref PRIMARY PRIMARY 4 tp1.programs.id 1 USING index

1 SIMPLE volunteerprojecttype ref PRIMARY PRIMARY 4 tp1.programs.id 6 USING index

1 SIMPLE workprogramtype ref PRIMARY PRIMARY 4 tp1.programs.id 10 USING index

1 SIMPLE languages ref PRIMARY PRIMARY 4 tp1.programs.id 4 USING index

1 SIMPLE monthavailable ref PRIMARY PRIMARY 4 tp1.programs.id 16 USING index

2

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.