Edit Query To Improve Performance

De query below takes like 9seconds to complete, which is not acceptable.

Can some body help me to edit this query to improve the performance

$dataProvider=new CActiveDataProvider('Daytrip', array(



			'GeoDistKM(ci.latitude, ci.longitude, ' . $result['latitude'] . ', ' . $result['longitude'] . ') AS distance',












			'GROUP_CONCAT(DISTINCT ca.category ORDER BY ca.id_category SEPARATOR ", ") AS categories',

			'GROUP_CONCAT(DISTINCT ta.tag ORDER BY ta.tag SEPARATOR ", ") AS tags',


		'join'=>'LEFT JOIN city ci ON ci.id_city = city_id LEFT JOIN daytrip_category dc ON id_daytrip = dc.daytrip_id

			LEFT JOIN category ca ON id_daytrip = dc.daytrip_id LEFT JOIN daytrip_tag dt ON id_daytrip = dt.daytrip_id

			LEFT JOIN tag ta ON ta.id_tag = dt.tag_id LEFT JOIN daytrip_category cf ON id_daytrip = cf.daytrip_id',

		'condition'=>'active = 1 AND cf.category_id IN (' . implode(',', $categories) . ')',


		'order'=>'1, city, priority DESC, RAND(CURDATE())',







First that comes to my mind when I hear about slow queries - if indexes are set up (correctly) ?

Also, you may use EXPLAIN SELECT to see which way query is executed and to find bottleneck(s)

BTW. There’s a really useful and informative book on mysql performance: OReIlly.High.Performance.MySQL.Second.Edition.Jun.2008 that covers topic in all details.