库存慢SQL查询

Question

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";

Answer 1

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.