始终进行数据库查询的最有效方法是什么，我知道只会偶尔返回结果？

Question

I am trying to work out how to implement a new piece of functionality on my website. My site allows people to download things uploaded by members of a group. The new piece of functionality will allow 1 member of the group to be a 'super' user whos uploads cannot be downloaded. So at the moment my query (more or less) is -

SELECT * FROM uploads WHERE group = $groupid

I am thinking I will store the 'super' status of the member in the 'members' table so will probably look up this to get the member id of the super member before doing the query.

$all_group_members = SELECT members FROM groups WHERE group_id = $groupid
$super_member = SELECT memberid FROM members WHERE memberid IN ($all_group_members) AND super_status = 1 

Then modify the query to be -

SELECT * FROM uploads WHERE group = $groupid AND uploader ! = $super_member

However there may not always be a super member in a group, so my question is is there a more effective way to do this, for example store the super members ids in the session cookie? What about storing the super user id in the group table - still involves doing a query -

SELECT super_user FROM groups WHERE groupid = $group_id 

But atleast thats one less query.

Is there just no way around this - I know that there will only be a super user about 5% of the time so 95% of the time this will be wasted query. Perhaps the session cookie could store a group_has_super_user variable.

Any other ways of doing this that im missing?

thanks

Answer 1

I assume, other group members can't download anything from my group's stuff. So, simply add a downloadable field to the uploads, and set it to

• 1, if a simple member has uploaded it, or
• 0, if a super member has.

Then the only query you have to run is:

SELECT * FROM uploads WHERE group = $groupid AND downloadable != 0

Also, if the membership is changing (e.g. someone else gets the super right), these flags should be recalculated in the group.

Don't forget to create index for (or set the primary key to) groupid + downloadable (or downloadable + groupid)!