Hi, I have a query which has a tendency to be slow.
This is because of a ‘with filesort’ and ‘with temporary’ in the EXPLAIN. I cant really figure out how to oprtimise this so that mysql does not use a filesort/temporary.
If I remove the ORDER BY, the query is quick, and as I would like it to be. Im wondering whether iI should remove the ORDER BY, and simply sort the results in my client language, in this case, its PHP.
Please can you advise.
297-MOD(sout.s_id, 293) sidmod,
17+MOD(sout.s_id, 89) sidmod2,
s., sq., sout.s_deliverystatusid, sout.s_deliverystatustext,
sout.s_statusid, sout.s_statustext, sout.s_messagetype, sout.s_partno, sout.s_id smsoutid, sout.s_queueid,
left join smsqueue sq on (sq.sq_requestid = s.s_requestid)
left join smsout sout on (sout.s_requestid = sq.sq_requestid and sout.s_partno = sq.sq_part)
s.s_userid = 17 and
s.s_datetime > DATE_SUB(CURRENT_TIMESTAMP, interval 1 month)
and ((sq.sq_id = (select MAX(innerq.sq_id) from smsqueue innerq where innerq.sq_part = sq.sq_part and innerq.sq_requestid = s.s_requestid)))
s.s_id, sq.sq_id, sout.s_id
Im not sure how much use the above query will be, since I havent posted my schema. But the EXPLAIN output is below
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s ref s_userid s_userid 5 const 2 Using where; Using temporary; Using filesort
1 PRIMARY sq ref sq_requestid sq_requestid 4 tran.s.s_requestid 1 Using where
1 PRIMARY sout ALL 8
2 DEPENDENT SUBQUERY innerq ref sq_requestid sq_requestid 4 tran.s.s_requestid 1 Using where; Using index
sms (s_id) PRIMARY
smsqueue (sq_id) PRIMARY
smsqueue (sq_requestid, sq_sent, sq_part)
smsout (s_id) PRIMARY
smsout (s_queueid, s_id)
Thanks for any advice. I come from a strong SQL Server background, but Im still struggling to take a MYSQL execution plan and see what indices thw query would benefit from.
On a sidenote, are there any tools to give a deeper insight into what mysql is doing under the hood. SQL Server has lots of tools I can use, but mysql only has EXPLAIN that I can see. Im not bashing mysql, its a great product, I just want to learn more.