hi all,

i have 2 tables article(rows>1 million) and comments(rows>1million)

some of articles have comments in comments table
i have indexed these tables using hibernate lucene indexing (annotations)
my requirement is when a user requests for an article i want to fetch the article from article table and the count of comments if made from comments table
here every article has unique art_code using which the comments count is obtained

for this i have written a view as select a.* , count(c.art_code) from article a left join comment c on c.art_code=a.art_code group by a.id

i have tested this view for just few rows( around 100 rows) but unfortunately this view takes lot of execution time when millions of rows are available in both tables

to fetch article from index it is taking very less time (1 or 2 seconds) but from index i can get comments count ..

so my questions are:

Apart from this view concept is there any other way to fetch comment count by joining these two tables

or how can i index these two tables by joining

thanks a lot
bye

you're fetching all articles? You mentioned you wanted to load the article selected by user.

thanks for your response
i want both

i have written the view for listing the latest articles with their comments count and individual article with comments count is needed when user requests for single article

bye