I think it's not fair to expect the list to reply with the kind of information you have provided. Are these tables indexed et all? What indices are you using? Have you tried to see what "explain" tell you about the plan the optimizer will use to execute the query?
Rest assured, you surely stand a better chance of reply if you provide information on the above set of questions. Cheers Manoj ----- Original Message ----- From: "Sebastian" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Thursday, August 11, 2005 3:52 PM Subject: Re: help with slow query > no one has any info to help me out? > all i need to know if there is a way to speed up the query or will i > have to live with it. > > > this query runs slow because AVG and COUNT on maps_rating table i think. > > can anything be done to improve? > > > > query: > > > > SELECT > > maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS > > votes, user.username > > FROM maps > > LEFT JOIN maps_rating ON (maps.id = maps_rating.map) > > LEFT JOIN user ON (user.userid = maps.userid) > > GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage > > > > structure: > > > > maps - rows: 700 > > +--+----+-----+-------+--------+--------+ > > | id | mip | map | userid | author | filename | > > +--+----+-----+-------+--------+--------+ > > > > > > maps_rating - rows: 2,000 > > +--+-----+------+--------+-----------+-------+ > > | id | map | rating | userid | ipaddress | dateline | > > +--+-----+------+--------+-----------+-------+ > > > > > > user - rows: 10,000 > > +------+-----------+ > > | userid | username | > > +------+-----------+ > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 8/9/2005 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
