I have following tables
Articles (id, title, detail,date) 
Rates (id, article_id, member_id, rate)
Hits (id, article_id)
Members (id, nickname)
*rate is 1,2,3,4 or 5
*Hits records how many times article has been viewed

I need to:
1) return most popular 25 articles within last seven days.  I need a
way to find popular article based rate,rate count and maybe hits
2) number of members rated individual article
3) current rate for individual article
4) Hit count to each article (views)
5) assuming user is logged in with nickname 'user', I need to know his
rate for individual article (if any)


PLEASE consider following
1) heavy traffic web site
2) mysql 4.1 
3) php 4.3
4) apache 1.3

** I NEED OPTIMIZED QUERY.  ALSO TABLE STRUCTURE AND INDEX **

Why not keep track of the number of hits on an article in the article
table instead of having to add a new row to the hits table every time
someone loads an article?

Also, as I'm looking (no way to edit posts), article_id and member_id
are enough to uniquely identify a row in the `Rates` table, noone is
going to need to rate an article twice, right?

Article	Rates		members
id	id		id
title	Arcticle-id		name
Detail	memberid		
date	rate		
hits			
rate(Everytime somebody rates a article , calculate the overall Rate
and keep it here , this will help in getting the Rates very fast ).


article.hits = index 
rates.rate = index 
article.rate = index 
		


Query for 			

1	"return most popular 25 articles within last seven days.  I need a
way to find popular article based rate,rate count and maybe hits
"	select top 25 from arcticle where Rate > some number 
		select top 25 from arcticle where hits > some number 

2		select count(memberid) from Rates where memebrid ! = null 

3		select count(hits)  from article where hits > some number 

4		select articleid ,articlename from rates,article,members where
member.name = user and member.id = rates.memberid and rates.articleid
= artcilce.id

create table articles(id INT NOT NULL,title VARCHAR(200),detail 
VARCHAR(200), date DATE, hits int);

create table rates (id  INT NOT NULL, article_id  INT NOT
NULL,member_id INT, rate INT);

create table members (id int not null,nickname VARCHAR(200))

prepare sample data
insert into members values (1, 'A');
insert into members values (2, 'B');
insert into members values (3, 'C');
insert into members values (4, 'D');
insert into members values (5, 'E');
insert into members values (6, 'F');
insert into members values (7, 'G');
insert into members values (8, 'H');
insert into members values (9, 'I');
insert into members values (10, 'J');
insert into members values (11, 'K');
insert into members values (12, 'L');
insert into members values (13, 'M');
insert into members values (14, 'N');
insert into members values (15, 'O');
insert into members values (16, 'P');
insert into members values (17, 'Q');
insert into members values (18, 'R');
insert into members values (19, 'S');
insert into members values (20, 'T');


insert into articles values (1, 'Article 1', 'Article details 1', CURDATE(), 10);
insert into articles values (2, 'Article 2', 'Article details 2', CURDATE()-1, 0);
insert into articles values (3, 'Article 3', 'Article details 3', CURDATE()-1, 1);
insert into articles values (4, 'Article 4', 'Article details 4', CURDATE()-2, 25);
insert into articles values (5, 'Article 5', 'Article details 5', CURDATE()-2, 22);
insert into articles values (6, 'Article 6', 'Article details 6', CURDATE()-2, 5);
insert into articles values (7, 'Article 7', 'Article details 7', CURDATE()-2, 22);
insert into articles values (8, 'Article 8', 'Article details 8', CURDATE()-3, 1);
insert into articles values (9, 'Article 9', 'Article details 9', CURDATE()-3, 11);
insert into articles values (10, 'Article 10', 'Article details 10',
CURDATE()-4, 55);
insert into articles values (11, 'Article 11', 'Article details 11',
CURDATE()-4, 14);
insert into articles values (12, 'Article 12', 'Article details 12',
CURDATE()-4, 5);
insert into articles values (13, 'Article 13', 'Article details 13',
CURDATE()-4, 11);
insert into articles values (14, 'Article 14', 'Article details 14',
CURDATE()-5, 23);
insert into articles values (15, 'Article 15', 'Article details 15',
CURDATE()-5, 12);
insert into articles values (16, 'Article 16', 'Article details 16',
CURDATE()-5, 16);
insert into articles values (17, 'Article 17', 'Article details 17',
CURDATE()-6, 29);
insert into articles values (18, 'Article 18', 'Article details 18',
CURDATE()-6, 34);
insert into articles values (19, 'Article 19', 'Article details 19',
CURDATE()-6, 17);
insert into articles values (20, 'Article 20', 'Article details 20',
CURDATE()-6, 48);
insert into articles values (21, 'Article 21', 'Article details 21',
CURDATE()-7, 0);
insert into articles values (22, 'Article 22', 'Article details 22',
CURDATE()-7, 0);
insert into articles values (23, 'Article 23', 'Article details 23',
CURDATE()-7, 5);
insert into articles values (24, 'Article 24', 'Article details 24',
CURDATE()-8, 9);
insert into articles values (25, 'Article 25', 'Article details 25',
CURDATE()-8, 2);
insert into articles values (26, 'Article 26', 'Article details 26',
CURDATE()-8, 0);
insert into articles values (27, 'Article 27', 'Article details 27',
CURDATE()-9, 2);
insert into articles values (28, 'Article 28', 'Article details 28',
CURDATE()-9, 1);
insert into articles values (29, 'Article 29', 'Article details 29',
CURDATE()-10, 9);
insert into articles values (30, 'Article 30', 'Article details 30',
CURDATE()-11, 10);

insert into rates values (1, 10, 7,  4);
insert into rates values (2, 10, 1,  1);
insert into rates values (3, 10, 2,  2);
insert into rates values (4, 11, 3,  3);
insert into rates values (5, 12, 4,  4);
insert into rates values (6, 10, 5,  5);
insert into rates values (7, 12, 6,  1);
insert into rates values (8, 10, 13,  1);
insert into rates values (9, 10, 14,  4);
insert into rates values (10, 10, 18,  4);
insert into rates values (11, 5, 15,  4);
insert into rates values (12, 5, 1,  2);
insert into rates values (13, 9, 3,  3);
insert into rates values (14, 9, 4,  1);
insert into rates values (15, 4, 5,  4);
insert into rates values (16, 14, 6,  5);
insert into rates values (17, 6, 7,  2);
insert into rates values (18, 30, 6,  3);
insert into rates values (19, 17, 7,  1);
insert into rates values (20, 17, 9,  2);
insert into rates values (21, 13, 6,  3);
insert into rates values (22, 13, 7,  4);
insert into rates values (23, 14, 9,  2);
insert into rates values (24, 15, 3,  1);
insert into rates values (25, 16, 4,  2);
insert into rates values (26, 16, 5,  1);
insert into rates values (27, 20, 11,  3);
insert into rates values (28, 20, 12,  3);
insert into rates values (29, 20, 13,  3);
insert into rates values (30, 20, 14,  5);
insert into rates values (31, 29, 8,  2);
insert into rates values (32, 30, 11,  1);
insert into rates values (33, 28, 2,  3);
insert into rates values (34, 25, 2,  4);
insert into rates values (35, 24, 2,  5);
insert into rates values (36, 23, 12, 2);
insert into rates values (37, 27, 3,  3);
insert into rates values (38, 28, 4,  3);
insert into rates values (39, 12, 5,  4);
insert into rates values (40, 13, 6,  1);

SOLUTION - 
1) return most popular 25 articles within last seven days.  I need a
way to find popular article based rate,rate count and maybe hits

NOTE - limit <n> here in following sql limit given to 25 as per
requirement u can change.
i. first 25 in last 7 days order by hits
	select * from articles where date > CURDATE()-7 order by hits desc, date limit 25;
ii. first 25 in last 7 days order by rate
	select a.*,avg(b.rate) rate from articles a, rates b where
a.id=b.article_id and a.date > CURDATE()-7 group by b.article_id order
by rate desc 25;
iii. first 25 in last 7 days order by rate count
	select a.*,count(b.article_id) as rate_count from articles as a,
rates as b where a.id=b.article_id and a.date > CURDATE()-7 group by
b.article_id order by rate_count desc limit 25;

2) number of members rated individual article
Average rating given to the article (10) by members 
NOTE- specify article no 
	select count(*) no_of_members from members a, rates b where
b.member_id=a.id and article_id=10;
	
	list of members -> select a.* from members a, rates b where
b.member_id=a.id and article_id=10;

3) current rate for individual article
Average rating given to the article (10) by members 
NOTE- specify article no.
	select avg(rate) from rates a, articles b where a.article_id=b.id and b.id=10;
	
4) Hit count to each article (views)
NOTE- specify article no.
	select hits from articles where id=10;	
	
5) assuming user is logged in with nickname 'user', I need to know his
rate for individual article (if any)
NOTE- specify article no. and user nickname
select article_id, avg(rate) rate from rates a, members b where
a.member_id=b.id and b.nickname='A' and article_id=10 group by
article_id;

NOTE - Create index on order by columns