Originally Posted by miXXXture
@camperjohn64
splitting is for the ads which have 2+ keywords... for example, if the ad is tagged to show for both "milf" and "lisa ann", we had to add one more row for each tag with same ad...
well, UNION was the one of solutions when i was searching, but it was quite slower than the current one... i'm not sure, maybe i'm missing something... as you said, the IN statement looks like the killer
@Tempest
i had index on ads.a_area, i'm not sure if it made any improvements...
about putting index on ads.a_imp... well, i did not mention that, with every query, ads.a_imp (impressions) getting updated, so, putting index on it probably will not do any good, right?
actually, i was trying to keep simple at first as you showed and with a few other ways... did not see any improvements...
@dubsix
you are right about GROUP BY... it somehow involved in the equation... i thought it could be better with less queries... with group by, i can get one ad for each spot in one query... 150k bad queries VS. 600k less bad queries...
with group by (0.0095 sec)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 69 Using temporary; Using filesort
2 DERIVED tags range PRIMARY,t_name t_name 152 NULL 4 Using where; Using temporary; Using filesort
2 DERIVED adtags ref PRIMARY PRIMARY 4 cfs_tags.t_id 8 Using index
2 DERIVED ads eq_ref PRIMARY PRIMARY 4 adtags.at_aid 1 Using where
without group by, which means running a query like below for each spot (in this case we have 4 spots)
it tooks 0.0237 sec
SELECT ads.a_id, ads.a_area, ads.a_code
FROM ads, adtags, tags
WHERE ads.a_id = adtags.at_aid AND adtags.at_tid=tags.t_id
AND tags.t_name IN ('sponsor1','big tits','milf','lisa ann','anal','default')
AND ads.a_area = 3
ORDER BY ads.a_imp ASC LIMIT 1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cfs_tags range PRIMARY,t_name t_name 152 NULL 4 Using where; Using temporary; Using filesort
1 SIMPLE cfs_adtags ref PRIMARY PRIMARY 4 cfsads.cfs_tags.t_id 8 Using index
1 SIMPLE cfs_ads eq_ref PRIMARY PRIMARY 4 cfsads.cfs_adtags.at_aid 1 Using where
|