Before I write this SQL query, I want to make sure that it's not going to take all of this century to execute. The reason I ask is because the actual query will involve joining together about 30 tables and limiting the results based on some constraints.
Most of the tables that I will be joining are just used to define relationships between other tables. For example:
user (user_id, name)
access_level (acc_id, level)
user_access_level (user.user_id, access_level.acc_id)
The user_access_level table just tells me the relationships between users and their access levels. There are a lot of these tables that I'll be joining. In fact, roughly 1/2 of the tables are like this.
With this in mind, do you think that joining together in total about 30-35 tables is going to be a huge performance problem?
Query Time
Moderator: General Moderators
- protokol
- Forum Contributor
- Posts: 353
- Joined: Fri Jun 21, 2002 7:00 pm
- Location: Cleveland, OH
- Contact:
Basically what I need is to do this. There are 4 levels of access in my system. There are Organizations, Business Units, Departments, and Users. The following tree outlines the structure.
So I want to create a query which basically returns all of this information. Then, given these results, I want to constrain the rows by checking to see if a certain access level exists in the results. I have two options. One of them is to write the single query which gets all of this information in a uber-resultset. The other option is to check for the 4 access types with 4 queries. One checks Organization access, one checks Business Unit access, another checks Department access, and the last one checks Individual User access.
Right now I'm thinking that using 4 queries might not be a bad idea. This way we only query when we absolutely need to. Worst case scenario is doing 4 queries. Is this worst-case worth it compared to the performance problems I might incur with the single query?
Code: Select all
Organization
|
|_ Business Unit
|
|_ Department
|
|_ Users
| |
| |_ Responsibilities
| | |
| | |_ Access Levels
| |
| |_ Access Levels
|
|_ Responsibilities
|
|_ Access LevelsRight now I'm thinking that using 4 queries might not be a bad idea. This way we only query when we absolutely need to. Worst case scenario is doing 4 queries. Is this worst-case worth it compared to the performance problems I might incur with the single query?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
depending on your mysql version, you could use a union...
or you could normalize the whole thing such that there is only 1 access levels table, with rows for everything user X can access.. you'd have to fiddle with this table every time you change the rights of a department for instance.. but, the query would be quite straight forward for if they have access
or you could normalize the whole thing such that there is only 1 access levels table, with rows for everything user X can access.. you'd have to fiddle with this table every time you change the rights of a department for instance.. but, the query would be quite straight forward for if they have access
- protokol
- Forum Contributor
- Posts: 353
- Joined: Fri Jun 21, 2002 7:00 pm
- Location: Cleveland, OH
- Contact:
I'll show you my table structure so you can see how I do everything:
Code: Select all
DROP TABLE IF EXISTS responsibility;
CREATE TABLE responsibility (
resp_id int(11) NOT NULL auto_increment,
title varchar(255) NOT NULL default '',
PRIMARY KEY (resp_id)
) TYPE=InnoDB COMMENT='Define a responsibility and associate it with a group';
DROP TABLE IF EXISTS user;
CREATE TABLE user (
user_id int(11) NOT NULL auto_increment,
username varchar(16) NOT NULL default '',
full_name varchar(255) NOT NULL default '',
join_date int(11) NOT NULL default '0',
PRIMARY KEY (user_id),
UNIQUE KEY username (username)
) TYPE=InnoDB COMMENT='Define organization staff here';
DROP TABLE IF EXISTS access_group;
CREATE TABLE access_group (
group_id int(11) NOT NULL auto_increment,
description varchar(25) NOT NULL default '',
PRIMARY KEY (group_id),
UNIQUE KEY description (description)
) TYPE=InnoDB COMMENT='Defines access level groupings';
DROP TABLE IF EXISTS access_level;
CREATE TABLE access_level (
acc_id int(11) NOT NULL auto_increment,
level varchar(255) NOT NULL default '',
description varchar(255) NOT NULL default '',
PRIMARY KEY (acc_id),
UNIQUE KEY level (level)
) TYPE=InnoDB COMMENT='All access levels are defined here';
drop table if exists access_level_in_group;
CREATE TABLE access_level_in_group (
acc_id int(11) not null,
group_id int(11) not null,
index (acc_id),
FOREIGN KEY (acc_id) REFERENCES access_level (acc_id) ON DELETE CASCADE,
index (group_id),
FOREIGN KEY (group_id) REFERENCES access_group (group_id) ON DELETE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS department;
CREATE TABLE department (
dept_id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
org_id int(11) NOT NULL default '0',
PRIMARY KEY (dept_id),
KEY org_id (org_id)
) TYPE=InnoDB COMMENT='Define departments, divisions, etc. here';
DROP TABLE IF EXISTS organization;
CREATE TABLE organization (
org_id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
PRIMARY KEY (org_id),
UNIQUE KEY name (name)
) TYPE=InnoDB COMMENT='Top-level company and business units are defined here';
DROP TABLE IF EXISTS department_member;
CREATE TABLE department_member (
dept_id int(11) NOT NULL default '0',
user_id int(11) NOT NULL default '0',
index (dept_id),
FOREIGN KEY (dept_id) references department (dept_id) on delete cascade,
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade
) TYPE=InnoDB COMMENT='Users who are in the group';
DROP TABLE IF EXISTS department_org_access;
CREATE TABLE department_org_access (
dept_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (dept_id),
foreign key (dept_id) references department (dept_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='Group-level organization access';
DROP TABLE IF EXISTS department_responsibility;
CREATE TABLE department_responsibility (
dept_id int(11) NOT NULL default '0',
resp_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (dept_id),
foreign key (dept_id) references department (dept_id) on delete cascade,
index (resp_id),
foreign key (resp_id) references responsibility (resp_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='Group responsibilities related to an organization';
-- DROP TABLE IF EXISTS department_structure;
-- CREATE TABLE department_structure (
-- dept_id int(11) NOT NULL default '0',
-- parent_dept_id int(11) NOT NULL default '0',
-- index (dept_id),
-- foreign key (dept_id) references department (dept_id) on delete cascade,
-- index (parent_dept_id),
-- foreign key (parent_dept_id) references department (dept_id) on delete cascade
--
-- ) TYPE=InnoDB COMMENT='Use to create multi-level groups (sub-structure)';
DROP TABLE IF EXISTS organization_business_unit;
CREATE TABLE organization_business_unit (
unit_id int(11) NOT NULL default '0',
company_id int(11) NOT NULL default '0',
index (unit_id),
foreign key (unit_id) references organization (org_id) on delete cascade,
index (company_id),
foreign key (company_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='For business unit definition only ... no sub-units allowed';
DROP TABLE IF EXISTS responsibility_access_level;
CREATE TABLE responsibility_access_level (
resp_id int(11) NOT NULL default '0',
acc_id int(11) NOT NULL default '0',
index (resp_id),
foreign key (resp_id) references responsibility (resp_id) on delete cascade,
index (acc_id),
foreign key (acc_id) references access_level (acc_id) on delete cascade
) TYPE=InnoDB COMMENT='Access levels associated with a responsibility';
CREATE TABLE `user_in_org` (
`user_id` INT NOT NULL ,
`org_id` INT NOT NULL ,
index ( `user_id` ),
foreign key (user_id) references user (user_id) on delete cascade,
index ( `org_id` ),
foreign key (org_id) references organization (org_id) on delete cascade
) type=innodb;
DROP TABLE IF EXISTS user_access_level;
CREATE TABLE user_access_level (
user_id int(11) NOT NULL default '0',
acc_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade,
index (acc_id),
foreign key (acc_id) references access_level (acc_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='User has access level associated with an organization';
DROP TABLE IF EXISTS user_org_access;
CREATE TABLE user_org_access (
user_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='User-level organization access';
DROP TABLE IF EXISTS user_responsibility;
CREATE TABLE user_responsibility (
user_id int(11) NOT NULL default '0',
resp_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade,
index (resp_id),
foreign key (resp_id) references responsibility (resp_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='User has responsibility related to an organization';