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
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
