How can I get the 'EXISTS' or 'IN' use an index?

[Johannes Fosseus]

Hi

I have two tables 'notice' and 'friend' - and one problem. I want to get all notice-info from my friends, the two queries here below does that but seems to be not to optimized. For example the dont use my indexes?

How can I do this better?

How can I get the 'EXISTS' or 'IN' use an index?

SELECT notice.*
FROM notice
WHERE EXISTS(
SELECT friendId
FROM friend
WHERE friend.userId = 76
AND friend.friendId = notice.who )
ORDER BY notice.created
DESC LIMIT 20


SELECT notice.*
FROM notice
WHERE who IN (263,195,137,171,148,159,198,232,237,165,245,228,262,260,261,248)
ORDER BY created
DESC LIMIT 20

Table notice have index on, 'who' and on 'created'
Table friendhave index on, 'userId' and on 'friendId'



Many thanks / johannes



Edited 1 time(s). Last edit at 07/30/2009 04:41AM by Johannes Fosseus.