CREATE TABLE structure looks like this:
CREATE TABLE `tag_ad_map` ( `ad_id` int(11) NOT NULL default '0', `tag_id` int(11) NOT NULL default '0', `termfreq` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`ad_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `tag_keys` ( `id` int(11) NOT NULL auto_increment, `tag` varchar(32) collate utf8_swedish_ci NOT NULL default '', `idf` double NOT NULL default '0', `url` varchar(64) collate utf8_swedish_ci NOT NULL default '', `termfreq` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `url` (`url`) ) ENGINE=MyISAM AUTO_INCREMENT=11374 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci 2008/3/5, Baron Schwartz <[EMAIL PROTECTED]>: > > Hi, > > > On Wed, Mar 5, 2008 at 9:11 AM, Johan Thorvaldsson <[EMAIL PROTECTED]> > wrote: > > I need help to optimize this following query. It runs very slow and I > cant > > find any direct errors in it. > > SELECT > > 1 * t1.termfreq as viktatantal, > > t1.tag, t1.url FROM tag_keys t1 > > LEFT JOIN tag_ad_map tm1 ON t1.id = tm1.tag_id > > LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id > > LEFT JOIN tag_keys t2 ON t2.id = tm2.tag_id > > WHERE > > t2.url = 'motor' AND t1.url != 'motor' > > GROUP BY t1.id > > ORDER BY viktatantal DESC, RAND() > > LIMIT 80 > > > > Any help would be very appriciated! > > > You should use EXPLAIN. If you don't understand the output of > EXPLAIN, post the output back to this list and we can help you > understand it. > > Cheers > > Baron >
