Sebastian 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
It seems to me that an index on maps_rating.rating, maps.userid,
user.userid might help. Also making the query "ALTER TABLE maps ORDER BY
maps.dateline DESC" once a day or more, would help the ordering.
I may be saying too include much indexes (probably the first), but it
may not make bad at all (probably updates/inserts would be slower).
Making some tests might help to see what is the best.
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
