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