From: Johan Thorvaldsson (oktorpgmail.com)
Date: Wed Mar 05 2008 - 09:59:26 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <baronxaprb.com>:
>
> Hi,
>
>
> On Wed, Mar 5, 2008 at 9:11 AM, Johan Thorvaldsson <oktorpgmail.com>
> 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
>

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]