I have a query that runs very slow and using Using temporary; Using
filesort. Is there a way to avoid them using current table structure?
Goal with the query is to find ads (ad_id) that have tags (tag_id) connected
to them, order by "antal" is used to get the most relevant first. ( the one
that conains all 5 tags and so on).
Query:
SELECT COUNT(*) antal,ad.ad_id FROM ad
LEFT JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id
WHERE
tm.tag_id IN (99, 10807, 20728, 21, 135)
AND ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert <= 5 OR
ad.whitelisted = 1)
AND tm.is_active=1 AND ad.ad_id != 32793
GROUP BY tm.ad_id ORDER BY antal DESC LIMIT 10
Explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tm
type: ref
possible_keys: PRIMARY,tag_id,ad_id,is_active
key: is_active
key_len: 4
ref: const
rows: 177800
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ad
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: rubbt.tm.ad_id
rows: 1
Extra: Using where
tag_ad_map:
Create Table: 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',
`weight` int(11) NOT NULL default '0',
`is_active` int(11) NOT NULL default '0',
PRIMARY KEY (`tag_id`,`ad_id`),
KEY `tag_id` (`tag_id`),
KEY `ad_id` (`ad_id`),
KEY `is_active` (`is_active`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
tag_keys:
Create Table: 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',
`weight` int(11) NOT NULL default '0',
`disable_in_cloud` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `url` (`url`),
KEY `tag` (`tag`)
) ENGINE=MyISAM AUTO_INCREMENT=49312 DEFAULT CHARSET=utf8
COLLATE=utf8_swedish_ci
