Quote:
Originally Posted by miXXXture
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? |
Put an index on ads.a_area and ads.a_imp
Not sure why you're doing a sub query which I suspect will end up being relatively slow.
I often find it's faster to do multiple queries as opposed to one "big" one.
So for example,
SELECT a.at_aid FROM adtags AS a LEFT JOIN tags AS b ON b.t_id=a.at_tid WHERE b.t_name IN ('sponsor1','big tits','milf','lisa ann','anal','default')
Then compile the at_aid results in list form (eg: a,b,c,d,e) and then
SELECT a_id, a_area, a_code FROM ads WHERE a_id IN (a,b,c,d,e) AND a_code IN (3,5,6,4) ORDER BY a_imp GROUP BY a_area
It's one of those things where, if there's a problem, you need to test things out to find the optimal queries.