No announcement yet.

Query optimization indexing help

  • Filter
  • Time
  • Show
Clear All
new posts

  • Query optimization indexing help

    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,2 23,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

  • #2
    I have gone through http://www.hackmysql.com/case1 through case5 and have tried different combinations that i thought should work but haven't so far. So any advise would be greatly appreciated.


    • #3
      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.