Query Time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Query Time

Post by protokol »

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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

shouldn't be too much of a problem.. although, it could run very slow if you have lots and lots and lots of rows that need searching for each one..

You may need to spend a LOT of time optimizing the whole query to quickly return the results you want.
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

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.

Code: Select all

Organization
|
|_ Business Unit
    |
    |_ Department
        |
        |_ Users
        |   |
        |   |_ Responsibilities
        |   |   |
        |   |   |_ Access Levels
        |   |   
        |   |_ Access Levels
        |
        |_ Responsibilities
            |
            |_ Access Levels
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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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 :)
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

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';
Post Reply