I have a view, vw_tc_user_acccess, for determing user access to certain
objects. On my machine, I get the following query execution times:
1. SELECT * FROM vw_tc_user_access: 33.04ms
2. SELECT * FROM vw_tc_user_access WHERE object_type = 'FORUM': 3.49ms
3. SELECT * FROM vw_tc_user_access WHERE object_type = 'CATEGORY': 107.53ms
Queries #2 and #3 are obviously a subset of #1, simply filtered by
object_type. My questions are:
1) How can #3 take 30 times as long as #2, given that in my databse they
have the exact same number of rows returned and are drawn from the exact
same tables?
2) How is it possible for #3, a subset of #1 with a very simple WHERE
clause, to take three times longer than #1 to execute?
The view definition is:
CREATE VIEW vw_tc_user_access AS
SELECT DISTINCT ur.user_id AS user_id,
arm.acl_action AS action,
ao.acl_object_type AS object_type,
ao.acl_object_key AS object_key
FROM tc_acl_role_map arm
INNER JOIN tc_acl_objects ao ON arm.acl_object_id = ao.acl_object_id
INNER JOIN tc_user_roles ur ON ur.role_id = arm.role_id;
I can provide table definitions if needed.
-j
--
Jeremy Jongsma
jeremy@jongsma.org
http://www.jongsma.org
