sql - mysql where IN on large dataset or Looping? -

i have following scenario:

 table 1:   articles   id article_text  category  author_id  1  "hello world"    4                1  2 "hi"              5                2  3 "wasup"           4                3    table 2   authors   id   name     friends_with  1    "joe"      "bob"  2    "sue"      "joe"  3    "fred"     "bob" 

i want know total number of authors friends "bob" given category.

so example, category 4 how many authors there friends "bob".

the authors table quite large, in cases have million authors friends "bob"

so have tried:

get list of authors friends bob, , loop through them , count each of them of given category , sum in code.

the issue approach can generate million queries, though fast, seems there should better way.

i thinking of trying list of authors friends bob , building in clause list, fear blow out amt of memory allowed in query set.

seems common problem. ideas?

thanks

select count(distinct auth.id) authors auth inner join articles art on auth.id = art.author_id friends_with = 'bob' , art.category = 4 

count(distinct a.id) required articles might hit multiple rows each author.

but if have control on database use link table friends_with cussrent solution either have use comma seperated list of names disastrous performance , require completly different query or each author can have 1 friend.

friends

id friend_id

then query this

select count(distinct auth.id) authors auth inner join articles art on auth.id = art.author_id inner join friends f on auth.id = f.id  inner join authors fauth on fauth.id = f.friend_id fauth.name = 'bob' , art.category = 4 

its more complex allow many friends, remeber, construct calls 2 rows in friends each pair, 1 joe bob , 1 bob joe.

you build differently make query more complex.