Yii Framework Forum: Edit Query To Improve Performance - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Edit Query To Improve Performance Rate Topic: -----

#1 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 18 January 2013 - 02:57 PM

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(
	'criteria'=>array(
		'select'=>array(
			'GeoDistKM(ci.latitude, ci.longitude, ' . $result['latitude'] . ', ' . $result['longitude'] . ') AS distance',
			't.id_daytrip',
			't.daytrip',
			't.street',
			't.number',
			't.phone',
			't.website',
			't.deeplink',
			't.youtube',
			'ci.city',
			'ci.latitude',
			'ci.longitude',
			'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) . ')',
		'group'=>'id_daytrip',
		'order'=>'1, city, priority DESC, RAND(CURDATE())',
		'limit'=>100,
		'together'=>false,
		'with'=>array()
	),
	'pagination'=>false,
));

0

#2 User is offline   yugene 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 505
  • Joined: 08-August 09

Posted 19 January 2013 - 01:28 PM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users