Hello.
I've created tables similar to your and the query runs fast enough on my test data (maybe I have good indexes). Please, provide the EXPLAIN output for your query and exact definitions of your tables (use SHOW CREATE TABLE). Sebastian <[EMAIL PROTECTED]> wrote: > 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 | > +------+-----------+ > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
