Re: Multiple Counts - Multiple Tables - 1 Result - Using Left Join

[Chris Stubben]

Hi,

> Note: Also, when used in my robust application the
> result takes about 20 seconds so if anybody can
> optimize this query PLEASE LET ME KNOW!

Yes this is way too long. First, I would add indexes to any columns named in a WHERE clause, so you are missing a few regular indexes.

members (department_id)
members (member_type_id)
attachments(member_id)
transactions(department_id)

Just add this to the create table, KEY (department_id), or after table creation.
create index mdi on members(department_id);


Second, this would run much faster if the members table was in normal form. Drop the member_type_id and colon delimited members_type_id string in the members table and create a new member_type_link table. Something like this.


DROP TABLE IF EXISTS `member_types_link`;
CREATE TABLE IF NOT EXISTS `member_types_link` (
`member_id` int(11) NOT NULL auto_increment,
`member_type_id` tinyint(2) NOT NULL default '1',
`rank` tinyint(2), ### this may not be needed, but member_type_id=rank 1 and member_types_id=rank 2,3,etc
UNIQUE KEY (member_id, member_type_id)
) TYPE=MyISAM COMMENT='Table containing member information' AUTO_INCREMENT=8 ;
INSERT INTO `member_types_link` VALUES (1, 1, 1);
INSERT INTO `member_types_link` VALUES (2, 6, 1);
INSERT INTO `member_types_link` VALUES (3, 4, 1);
INSERT INTO `member_types_link` VALUES (3, 2, 2);
INSERT INTO `member_types_link` VALUES (3, 6, 3);
INSERT INTO `member_types_link` VALUES (4, 11, 1);
INSERT INTO `member_types_link` VALUES (4, 6, 2);
INSERT INTO `member_types_link` VALUES (4, 13, 3);
INSERT INTO `member_types_link` VALUES (5, 11, 1);
INSERT INTO `member_types_link` VALUES (5, 13, 2);
INSERT INTO `member_types_link` VALUES (6, 12, 1);
INSERT INTO `member_types_link` VALUES (6, 4, 2);
INSERT INTO `member_types_link` VALUES (6, 5, 3);
INSERT INTO `member_types_link` VALUES (7, 6, 1);
INSERT INTO `member_types_link` VALUES (7, 5, 2);


If you can change the schema, the query would be pretty easy...

select d.department_name,
count(if(mt.member_type_name="Executive", 1,NULL)) as Executives,
count(if(mt.member_type_name="Manager", 1,NULL)) as Managers,
count(if(mt.member_type_name="Rep", 1,NULL)) as Reps
from departments d, members m, member_types_link mtl, member_types mt
where d.department_id=m.department_id
and m.member_id=mtl.member_id
and mtl.member_type_id=mt.member_type_id
and d.department_name='PDQ'
group by 1;


+-----------------+------------+----------+------+
| department_name | Executives | Managers | Reps |
+-----------------+------------+----------+------+
| PDQ | 3 | 1 | 1 |
+-----------------+------------+----------+------+


Third, you can get counts of attachments and transactions using subqueries (or return all possible rows like you are doing (the Cartesian product from all tables in the join) and use COUNT DISTINCT which is also slow)


Chris