GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Count Distinct Query Hangs

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

    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.

    Comment

    Working...
    X