# SELECT ... LEFT JOIN and INDEX optimisation question

**URL:** <https://forums.percona.com/t/select-left-join-and-index-optimisation-question/228>\
**Category:** Other MySQL® Questions\
**Created:** [March 6, 2007, 9:33am UTC](https://forums.percona.com/t/select-left-join-and-index-optimisation-question/228 "2007-03-06T09:33:17Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![rigo](https://avatars.discourse-cdn.com/v4/letter/r/a5b964/32.png) [@rigo](https://forums.percona.com/u/rigo)\
**Post date:** [March 6, 2007, 9:33am UTC](https://forums.percona.com/t/select-left-join-and-index-optimisation-question/228/1 "2007-03-06T09:33:17Z")

</div>

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