i'm working on a simple contextual ad rotator for my sites... and i'm not sure if there is anyway to improve/optimize it on the database side...
well, there are 3 tables... ads, category/ads match and categories.
table 1: ads
- a_id (PRIMARY)
- a_area
- a_code
- a_imp
table 2: adtags
- at_tid
- at_aid
PRIMARY (at_tid, at_aid)
table 3: tags
- t_id (PRIMARY)
- t_name (Unique)
so, i get keywords and ad spots from the page, then put them into a query like that:
SELECT a.a_id, a.a_area, a.a_code FROM (
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 IN (3,5,6,4)
ORDER BY ads.a_imp ASC
) AS a
GROUP BY a.a_area
i've been trying it last couple days, not a complete disaster but i'm not sure if it can be improved or how it will act with growing tables and growing traffic....
currently, 450 records on the ads table and probably it will be around 2000 at max, and 500-600 records for keywords...
150k-160k pageviews/day... 700-800 new records at slow query log everyday, i assume there are some other factors which may affect that... etc.
so, what do you think? am i on the right way? the query or tables need anything? or i do need totally different approach?