I was wondering if someone could tell me what things I might need to do
to make this query as fast as possible.
I am developing a web site where users will have access to certain
things based on what groups they are in and what groups have access to
certain things. There are several different types things they have
access to based on the group but for this discussion lets limit it to
pages. For a user to view a page they have to be associated with one or
more of the groups that the page is linked to.
Since the relation ship between pages to groups and users to groups is
many to many I have a table just for that relationship. So here are my
tables
Group:
contains various info about the group with key field GroupID.
User:
contains various info about the user along with the key field UserID.
Page:
contains various info about a page on the site along with it's PageID.
GroupLink:
CREATE TABLE `grouplink` (
`LinkType` set('user','page','template','templatefile','menu') NOT
NULL default '',
`ID` int(10) unsigned NOT NULL default '0',
`GroupID` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`LinkType`,`ID`,`GroupID`)
) ;
Since there are several things that will be linked to groups I decided
to use one table to create all links and the The "LinkType" field to
designate which think we are linking to a group.
For example suppose I had page 18 linked to group 2, 5, 6, and 7 and I
had User 23 linked to group 1, 2, and 9. The rows in the table would be
like this....
group, 18, 2
group, 18, 5
group, 18, 6
group, 18, 7
user, 23, 1
user, 23, 2
user, 23, 9
Now I want to know if user 23 can access page 18 so I execute this query
SELECT COUNT(`GroupID`)
FROM `grouplink` u
JOIN `grouplink` p USING(`GroupID`)
WHERE u.`LinkType` = 'user' AND p.`LinkType` = 'page'
AND u.`ID` = '23' AND p.`ID` = '18'
Since User 23 and Page 18 are both linked to group 2, COUNT(`GroupID`)
should return 1.
The way the rules I have set work, if the count is 1 or larger then that
user has access to the page.
Now the question is there anything I can do to make this query faster?
--
Chris W
KE5GIX
"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm";
Ham Radio Repeater Database.
http://hrrdb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
