# sort in client lang (php) instead of mysql **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 5, 2006, 5:48pm UTC](https://forums.percona.com/t/sort-in-client-lang-php-instead-of-mysql/114 "2006-11-05T17:48:57Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)\ **Post date:** [November 5, 2006, 5:48pm UTC](https://forums.percona.com/t/sort-in-client-lang-php-instead-of-mysql/114/1 "2006-11-05T17:48:57Z")
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 ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [November 12, 2006, 11:31pm UTC](https://forums.percona.com/t/sort-in-client-lang-php-instead-of-mysql/114/2 "2006-11-12T23:31:52Z")
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.