Suggestion for optimization mysql query -

Desc: Complaint_stats contains several lines for special complaints, which are closed by the user (status = 2 and value IN ( 1, 2)) Pain point:

YEAR (cs.created_at) = year (curdate) and month (cs.created_at) = months (curdate)

Take about 5 minutes on the local and 1.5 minutes on the server

  select u3.name, COUNT (c3.id) stop as closed C3 From, Complaint_Status CS, Assignment A3, User U3 WHERE c3.id = cs.complaint_id and a3.complaint_id = C3.id and u3.id = a3.SineredTo and U 3.User_type = 14 and A3. Expiry taps and CISID in (Complaint_States WHERE complaint_ID = c3.id and condition = 2 and value IN (1,2)) and year (Cs.created_at) = year (curdate ()) and month (cs.created_at) = month by curdate ()) group by u3.id order COUNT (c3.id) DISE  

First, you should correct the join position.

Second, the inside position:

YEAR (cs.created_at) = year (curdate) and month (cs.created_at) = months (curdate ())

Use:

  cs.created_at> gt; Curdate () and cs_created_at & lt; Date_sub (date_sub, curdate), interval day (curdate ()) - 1, interval 1 month)  

This will allow the field to use any index that is created_at .

This seems to be your specific question.

If you really want to customize the query, then I suggest that ask with another question:

• Clearly Matching queries
• Table layout (or at least existing indexed)
• Interpretation plan for query.