Help to see how to simplify this MySQL statement, and optimize query efficiency ~~~~~

This post was last edited by Setoy on 2013-12-19 12:03:34

There are three tables: administrator tables, departmental tables, and administrator-to-department scoring tables, as follows:


Now to check the score of each department and statistics, even if a department does not have a score record to show his score (0 points), the results of the query statistics are as follows:


MySQL statement has been smattering, Patchwork wrote a paragraph:

Select   ' as type ', '   class '. ' Name ' as CNAME,   ' score '. ' CID ', '   score '. ' uid ', '   user '. ' Name ' as uname ,  ' score ' from ' score ' right joins ' class ' on ' score '. ' CID ' = ' class '. ' CID ' left join ' user ' on ' score '. ' UID ' = ' user '. ' UI d ' UNION All (  select     ' Total ',     ' C '. ' CNAME ', ' C '. ' CID ', '     C '. ' UID ',     count (' CID ') as uname,    sum (' score ') as score from  (    select       ' as type ', '       class '. ' Name ' as CNAME,       ' score '. ' CID ') , '       score '. ' uid ',       ' user '. ' Name ' as uname,      ' score ' from    ' score ' right    join ' class ' on ' score '. ' CID ' = ' class '. ' CID ' left    join ' user ' on ' score '. ' UID ' = ' user '. ' uid '  , C Group by ' CNAME ', order BY ' CNAME ' ASC , ' type ' ASC;

It seems that this SQL statement is too complex! Can you simplify this statement? Also from the query efficiency can not be optimized?
Table structure, data, and code are here: HTTP://SQLFIDDLE.COM/#!2/6F5DB6/10
Please help to see

Reply to discussion (solution)

Can you implement it with a simple SQL + PHP program?

Desc SQL
For key fields, add an index to see

Desc SQL
For the key field, plus the index to see if it can be specific point?

