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
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
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
Thanks baron for you reply. Here is the result from the explain:
1 SIMPLE t2 ref PRIMARY,url url 194 const 1 Using where; Using temporary;
Using filesort
1 SIMPLE tm1 index PRIMARY PRIMARY 8 NULL 149115 Using index
1 SIMPLE t1 eq_ref PRIMARY,url PRIMARY 4 rubbetdev.tm1.tag_id 1 Using where
1
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` (
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
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
