# Query optimization indexing help

**URL:** <https://forums.percona.com/t/query-optimization-indexing-help/2637>\
**Category:** Other MySQL® Questions\
**Created:** [April 18, 2013, 11:45am UTC](https://forums.percona.com/t/query-optimization-indexing-help/2637 "2013-04-18T11:45:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sundeep](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@sundeep](https://forums.percona.com/u/sundeep)\
**Post date:** [April 18, 2013, 11:45am UTC](https://forums.percona.com/t/query-optimization-indexing-help/2637/1 "2013-04-18T11:45:36Z")

</div>

Hi all,

Below is one of the slow query that we see in our slow query log. The generated query is the internal query that is done by an extension in an open source cms. Can anyone give me a pointer on what index combination would make the below query use index to sort the result of the join instead of using file sort? Any other optimization pointers would be appreciated too. all the columns referenced in the where , join and order by columns already have individual index on them

SELECT DISTINCT tt\_news.uid, tt\_news.\* FROM tt\_news LEFT OUTER JOIN  
tt\_news\_cat\_mm ON tt\_news.uid = tt\_news\_cat\_mm.uid\_local WHERE 1=1 AND  
tt\_news.sys\_language\_uid IN (0,-1) AND  
(IFNULL(tt\_news\_cat\_mm.uid\_foreign,0) IN  
(194,195,196,197,198,200,201,202,204,205,206,209,223,224,232,233,234,235,236,237,238))  
AND tt\_news.pid IN  
(3282,3281,3280,3279,3470,3278,3277,3295,3313,3312,3311,3310,3309,3308,3307,3306,3305,3304,3303,3302,3301,3300,3299,3298,3471,3297,3296,3314,3332,3331,3330,3329,3328,3327,3326,3325,3324,3323,3322,3321,3320,3319,3318,3317,3472,3316,3315,3333,3351,3350,3349,3348,3347,3346,3345,3344,3343,3342,3341,3340,3339,3338,3337,3336,3473,3335,3334,3352,3370,3369,3368,3367,3366,3365,3364,3363,3362,3361,3360,3359,3358,3357,3356,3355,3354,3353,3371,3389,3388,3387,3386,3385,3384,3383,3382,3381,3380,3379,3378,3377,3376,3375,3374,3373,3372,3390,3408,3407,3406,3405,3404,3403,3402,3401,3400,3399,3398,3397,3396,3395,3394,3393,3392,3391,3409,3427,3426,3425)  
AND tt\_news.deleted=0 AND tt\_news.t3ver\_state\<=0 AND tt\_news.pid\<\>-1 AND  
tt\_news.hidden=0 AND tt\_news.starttime\<=1366239780 AND (tt\_news.endtime=0  
OR tt\_news.endtime\>1366239780) AND (tt\_news.fe\_group=‘’ OR  
tt\_news.fe\_group IS NULL OR tt\_news.fe\_group=‘0’ OR  
FIND\_IN\_SET(‘0’,tt\_news.fe\_group) OR FIND\_IN\_SET(‘-1’,tt\_news.fe\_group))  
ORDER BY datetime desc LIMIT 10

---

<div class="post-metadata">

**Author:** ![sundeep](https://avatars.discourse-cdn.com/v4/letter/s/7feea3/32.png) [@sundeep](https://forums.percona.com/u/sundeep)\
**Post date:** [April 18, 2013, 11:56am UTC](https://forums.percona.com/t/query-optimization-indexing-help/2637/2 "2013-04-18T11:56:20Z")

</div>

I have gone through [url][http://www.hackmysql.com/case1[/url]](http://www.hackmysql.com/case1%5B/url%5D) through case5 and have tried different combinations that i thought should work but haven’t so far. So any advise would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [April 19, 2013, 5:20am UTC](https://forums.percona.com/t/query-optimization-indexing-help/2637/3 "2013-04-19T05:20:04Z")

</div>

Because of the combination of DISTINCT and ORDER BY (on different columns than the selected columns), this query will always require manual sorting by the rdbms.