A late followup on this, so I top post to keep the history intact. The composite primary key was the problem. Or rather, the missing individual indexes for tag_id and ad_id.
We also changed to INNER JOINs instead, but that didn't affect the performance. Thanks for all suggestions! On Wed, Mar 5, 2008 at 6:10 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > Also you have a composite key on for the prymary key in tag_keys . > ad_id should probably be a seperate index for > "LEFT JOIN tag_ad_map tm2 ON tm1.ad_id = tm2.ad_id " to join well. The > Index should be ignored because the left most portion of the the index > is not used... > > > > On 3/5/08, Rob Wultsch <[EMAIL PROTECTED]> wrote: > > From a brief glance: > > 1 * seems odd to me. Is this an attempt at some sort of cast? > > "ORDER BY viktatantal DESC, RAND() > > LIMIT 80" > > How many results would this return without the limit. The ORDER BY RAND() > > will never help a query. All the possible results have to be computed... > > > > Do you mean LEFT JOIN, or do you really mean INNER JOIN? If any of > > those LEFT joins fail then the result is not excluded by the join. > > until the where conditions come into play. > > > > I would rebuild the query with inner joins and the first table being t2, I > > would then rerun the explain and consider composite indexes. > > > > Mysql is not know for self join tables well. > > -- > > Rob Wultsch > > > 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! > > > > > > 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 -- Mvh Johan Sölve ____________________________________ Montania System AB Halmstad Stockholm Malmö http://www.montania.se Johan Sölve Mobil +46 709-51 55 70 [EMAIL PROTECTED] Kristinebergsvägen 17, S-302 41 Halmstad, Sweden Telefon +46 35-136800 | Fax +46 35-136801 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
