开发者

Efficiency of multiple equality tests vs joins in MySQL

Say I wanted to query data from a content table based on the user_id associated with that content - but I wanted to get content by more than one user.

SELECT field1, field2, ... fieldn
FROM content
WHERE user_id=1 OR user_id=2 OR ...

It seems to me that this is relatively efficient... up to a point. Say the content table had >200k records, and I was interested in records from 50 specific users. Would a query like the following still be efficient?

SELECT field1, field2, ... fieldn
FROM content
WHERE user_id=1 OR ... OR user_id=50

I'm considering creating a users_group table, like this:

CREATE TABLE us开发者_StackOverflow社区ers_group (
  group_id int(11),
  user_id int(11),
  primary_key(group_id,user_id)
);

... and populating it with groups of users the software is interested in.

I could then do a query like this:

SELECT field1, field2, ... fieldn
FROM content c
INNER JOIN users_group ug ON c.user_id=ug.user_id
WHERE ug.group_id=1

Would this be more efficient?


Or, something even easier:

SELECT field1, field2, ... fieldn
 FROM content
 WHERE user_id IN (1,2,3,4,5,6,7...50)

Build as dynamic SQL from something like PHP is a snap this way.

  • added: also, pretty decent until you get to > 1000 or so items. You'll also have to watch for MAX_PACKET, but this is the easiest/fastest way to do it for things in that range.


I think it is hard to say whether it would be quicker or not, the database still has to do the same work in the end, but it may be able to do it more efficiently with a join algorithm than with index seeks, you'd have to benchmark it.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜