Posted by Gordan on 10/28/06 12:36

hi all,

I need to select a random row from the "top 10" rows ordered by some key. Ie
there's a table with people and they have some points ...... I don't want to say
"the person with the most point is Gregor" I want "One of the top people is
Gregor" because he is one of the top 10 but amongst those I want a random row.
I had no trouble writing the query, but its slooooooooooooooooow so I need some
help in optimising it. Here's the query

SELECT query FROM (SELECT query, count(query) as count FROM search_history WHERE
module = 'tags'
GROUP BY query ORDER BY count DESC LIMIT 0, 10) as queries ORDER BY RAND() LIMIT
0, 1

tbl struct
CREATE TABLE search_history (
id int(11) NOT NULL auto_increment,
`query` varchar(255) NOT NULL default '',
username varchar(255) NOT NULL default '',
referer varchar(255) NOT NULL default '',
module varchar(255) NOT NULL default '',
`timestamp` varchar(10) NOT NULL default '',
PRIMARY KEY (id),
KEY `query` (`query`),
KEY module (module)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;thanks!Gordan

[Back to original message]