Announcement

Announcement Module
Collapse
No announcement yet.

SELECT ... LEFT JOIN and INDEX optimisation question

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SELECT ... LEFT JOIN and INDEX optimisation question

    Hi,

    I'm in trouble with making a performace optimization of a query:

    SELECT ca. * , ra.rasse, IF( cp.points > 0, cp.points, 0 ) AS points_nulled, IF( cp2.points > 0, cp2.points, 0 ) AS points2, cp2.point_desc FROM classifieds_ads ca LEFT JOIN rassen ra ON ra.id = ca.race LEFT JOIN classifieds_points cp ON cp.ad_id = ca.ad_id AND cp.point_id =5 LEFT JOIN classifieds_points cp2 ON cp2.ad_id = ca.ad_id AND cp2.point_id =10 WHERE exp_date > 1173189770 AND valid =1 AND (cp.point_id =5 OR cp.point_id IS NULL) ORDER BY points_nulled DESC , ca.add_date DESC LIMIT 1180 , 20
    EXPLAIN tells me the following:

    id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE ca ref exp_date,valid,exp_date_2 valid 1 const 2696 Using where; Using temporary; Using filesort1 SIMPLE ra ref id id 3 ca.race 1 Using index1 SIMPLE cp ref ad_id,point_id,ad_id_2 ad_id_2 5 ca.ad_id 2 Using where; Using index1 SIMPLE cp2 ref ad_id,point_id,ad_id_2 ad_id 5 ca.ad_id 2 Using where
    It's a listview of some ads left joined by some points.

    How is it possible to restructure this query so that it doesn't use filesort (and temporary)?

    Best regards
    rigo
Working...
X