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