Reputation: 2316
I have table called page
which represents every single page in my website.
page_id | page_subject | page_path
----------------------------------
1 | Foo | /Foo
2 | Bar | /Bar
I also have table called group
:
group_id | group_name
---------------------
1 | Users
2 | Admins
Goal: how to define access to every page for single group?
I did create another table - page_group
:
page_group | group_id
---------------------
1 | 1
1 | 2
Table contains groups that have access to page (page_group
).
I wrote function:
CREATE FUNCTION `PAGE_ACCESS`(`pageId` INT, `groupId` MEDIUMINT)
BEGIN
RETURN IF((SELECT COUNT(*) FROM `page_group` WHERE page_id = pageId AND group_id = groupId) > TRUE, FALSE);
END;
Now I can do something like that:
SELECT *
FROM page
WHERE <conditions> AND PAGE_ACCESS(page_id, <group_id>)
...which returns pages that I should have access to.
Works fine but seems to be slow with with rows > 100,000 How would you do that? Would you do that in a different way?
Upvotes: 2
Views: 58
Reputation: 25390
try:
SELECT *
from Page p
WHERE EXISTS (SELECT 1 from page_group pg join group g
on (pg.group_id = g.groupidId)
WHERE g.group_id = <groupId>
AND p.page_id = pg.page_id)
Also you can try (just realized that groups table does not required):
SELECT *
from Page p
WHERE p.page_id IN (Select page_id
from page_group pg
WHERE pg.group_id = <group_id>)
or even:
SELECT p.*
from Page p JOIN page_group pg ON (p.page_id = pg.page_id)
WHERE pg.group_id = <group_id>
Upvotes: 2
Reputation: 92792
I would test the speed of the query with EXPLAIN SELECT
- I'm guessing you don't have indexes on the tables? Another speculation: an index on (page_group.page_group,page_group.group_id)
might speed things up. See this introduction on EXPLAIN.
In addition, see @Michael Pakhantsov's answer with the subquery - it's easier for the server to optimize a subquery than a UDF.
Upvotes: 0