Stock with slow SQL query

0

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

Link: Stock with slow SQL query
Source: Stack Sql

Share.

About Author

Leave A Reply