sort in client lang (php) instead of mysql

MySQL & MariaDB Other MySQL® Questions
1

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.

Thanks

Query

select
297-MOD(sout.s_id, 293) sidmod,
17+MOD(sout.s_id, 89) sidmod2,
s.s_datetime datestamp,
sout.s_datetime smsoutdatetime,
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,
sout.s_manuallyconfirmed
from
sms s
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)
where
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)))
order by
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

EXPLAIN

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

INDICES

sms (s_id) PRIMARY
sms (s_userid)

smsqueue (sq_id) PRIMARY
smsqueue (sq_touserid)
smsqueue (sq_requestid, sq_sent, sq_part)

smsout (s_id) PRIMARY
smsout (s_jobid)
smsout (s_userid)
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.

Thankyou

2

Hi,

In your query you’re doing sort by tables from different columns which in case of MySQL requires it to use filesort, and filesort with join means temporary table will be used in most cases.

So this query per say can’t be simply optimized you need to change schema/query to get what you’re looking for

Ie denormalizing data so you can do order by on first table in join only can help a lot.