库存慢SQL查询

I have been working with the same SQL query for a couple of hours and it is finally working. But, it is very slow.. I have been trying to optimize it, but no luck, any help. Here is the query (Lots of left joins...):

 $sql ="SELECT u.id, u.display_name, IFNULL(SUM(r.total_rating)/COUNT(r.total_rating), 0) AS avg_rating, s.title AS study FROM users u
  LEFT JOIN rating r ON u.id = r.user_id 
  LEFT JOIN usermeta m ON u.id = m.user_id
  LEFT JOIN usermeta m1 ON u.id = m1.user_id
  LEFT JOIN studies s ON m.meta_value = s.id
  WHERE m.meta_key = 'study' AND m1.meta_key = 'subjects' AND m1.meta_value REGEXP '$subjectsvalues'
  GROUP BY u.id, r.total_rating
  ORDER BY avg_rating DESC
  LIMIT 10";

Table structure for user table:

id | display_name | email
-------------------------
 1 | Khar         | ...
 2 | SantaCruz    | ...

Table structure for rating table:

id | rating_title | total_rating  | user_id
-------------------------------------------
 1 | dffd         | 5             | 1
 2 | fddfdffdd    | 4             | 1

Table structure for usermeta table:

id | user_id | meta_key  | meta_value
-------------------------------------
 1 | 1       | study     | 132
 2 | 1       | subjects  | 121,231

Table structure for studies table:

id | title
----------
 1 | dsdsf
 2 | sdfdf

Subject values are handled like so:

$subjectsvalues = '';

$subjects = explode(",", $subjects);
foreach($subjects as $val) {
    $subjectsvalues = $subjectsvalues.",".$val.",|";
}
$subjectsvalues = $subjectsvalues."notdata";

First, left joins are unnecessary. So try this:

SELECT u.id, u.display_name, AVG(r.total_rating) AS avg_rating, s.title AS study
FROM users u JOIN
     rating r
     ON u.id = r.user_id JOIN
     usermeta m
     ON u.id = m.user_id JOIN
     usermeta m1 
     ON u.id = m1.user_id JOIN
     studies s
     ON m.meta_value = s.id
WHERE m.meta_key = 'study' AND m1.meta_key = 'subjects' AND m1.meta_value REGEXP '$subjectsvalues'
GROUP BY u.id, r.total_rating
ORDER BY avg_rating DESC
LIMIT 10;

Then, I would be inclined to try indexes on usermeta(meta_key, user_id, meta_value). I assume the main ids in the tables are all primary keys.

I can't suggest an edit to Gordon Linoff's answer, so here's an improved version. You don't really need to join with usermeta twice unless usermeta is being compared with itself.

SELECT u.id, u.display_name, AVG(r.total_rating) AS avg_rating, s.title AS study
FROM users u JOIN
     rating r
     ON u.id = r.user_id JOIN
     usermeta m
     ON u.id = m.user_id JOIN
     studies s
     ON m.meta_value = s.id
WHERE m.meta_key = 'study' OR (m.meta_key = 'subjects' AND m.meta_value REGEXP '$subjectsvalues')
GROUP BY u.id, r.total_rating
ORDER BY avg_rating DESC
LIMIT 10;

Also, could you please explain the utility of your regular expression? It most likely won't match anything as $ indicating end of the pattern is placed at start of regex.