Chris, this should already be pretty fast as it is using a primary key in
its entirety, and as long as the index size remains manageable MySQL will be
able to keep it in memory for fast access.
That said, doing away with the aggregate function might speed things up just
slightly.
You don't care how many matches there are, or which match provided access
(right?) - you just care whether there is or is not a match.
So, perhaps you could do this instead:
SELECT 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'
LIMIT 1
and if you get a result, the user has access; if you get an empty set, the
user has no access.
By not COUNTing and using a limit 1, you let the database answer your
question without examining more rows than it needs to.
Make sense?
-Dan
On Fri, Feb 29, 2008 at 9:31 PM, Chris W <[EMAIL PROTECTED]> wrote:
> 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]
>
>
