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.
Comments
Post a Comment