Hello.
> i thought i had an index on maps_rating.map which i didn't.. adding an > index on it improved the query. Have a look here: http://dev.mysql.com/doc/mysql/en/order-by-optimization.html http://dev.mysql.com/doc/mysql/en/group-by-optimization.html Sebastian <[EMAIL PROTECTED]> wrote: > well i managed to solve the problem myself, and im no sql genius... > i thought i had an index on maps_rating.map which i didn't.. adding an > index on it improved the query. > > i think that is about all the improvement i can get.. but if there is > still room for more speed i'd like to know.. > > Sebastian wrote: > >> Jigal van Hemert wrote: >> >>> Sebastian wrote: >>> >>>> this query runs slow because AVG and COUNT on maps_rating table i >>>> think. >>>> can anything be done to improve? >>> >>> >>> >>> You may want to include: >>> - table definitions (output of SHOW CREATE TABLE <table>) >>> - output of EXPLAIN <query> >> >> >> sorry for the lack of info. >> there are a couple of indexes on maps table, but i am not using them >> unless i use a where clause. >> >> not sure if the format is going to appear correctly on mailing list >> email, here is the info: >> >> id select_type table type possible_keys key >> key_len ref rows Extra >> 1 SIMPLE maps ALL /NULL/ /NULL/ /NULL/ >> /NULL/ 626 Using temporary; Using filesort >> 1 SIMPLE maps_rating ALL /NULL/ /NULL/ >> /NULL/ /NULL/ 1839 1 SIMPLE user eq_ref >> PRIMARY PRIMARY 4 site.maps.userid 1 >> >> CREATE TABLE `maps` ( >> `id` int(10) unsigned NOT NULL auto_increment, >> `mip` smallint(6) NOT NULL default '0', >> `map` varchar(50) NOT NULL default '', >> `userid` int(10) unsigned NOT NULL default '0', >> `filename` varchar(50) NOT NULL default '', >> `date` int(10) unsigned NOT NULL default '0', >> PRIMARY KEY (`id`), >> KEY `mip` (`mip`) >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=740; >> >> CREATE TABLE `maps_rating` ( >> `id` int(10) unsigned NOT NULL auto_increment, >> `map` int(10) NOT NULL default '0', >> `rating` smallint(6) NOT NULL default '0', >> `userid` int(10) unsigned NOT NULL default '0', >> `ipaddress` varchar(15) NOT NULL default '', >> `dateline` int(10) unsigned NOT NULL default '0', >> PRIMARY KEY (`id`) >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1884; >> >> the 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 >> > > -- 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]
