Help with speeding a query up.

[timw]

Hi guys I'm looking for a little help with this query I've been looking at it a little too long and would like a second set of eyes!

 

I have attached the code to create the tables and insert the data with my code to return the result as I would expect.

 

The issue is that my database is getting quite a few entries now BUT not as many as i hope to have in it and already the query is slow.

 

I'm sure there is a better way of writing the query and any comments on my code would be appreciated!

 

 

 

my OBJECT is to get a list AGENTS (id, cname) that have not yet selected Operator_2.

 

just so you notice AGENTS are in the login_user table with the user_level of 3.

 

My query would return the result that Agent_4 (user_id =8 ) is the only Agent that has not yet selected Operator_2.

 

/* 
login_users Table
user_id		user_level		cname  
1    		1			Admin  
2    		2			Operator_1  
3    		2			Operator_2  
4    		2			Operator_3
5    		3			Agent_1  
6    		3			Agent_2  
7    		3			Agent_3
8    		3			Agent_4	


selected_operators Table
id	user_id			operator_id  
1	5			2				// Agent_1 HAS SELECTED Operator_1
2	5			3				// Agent_1 HAS SELECTED Operator_2
3	5			4				// Agent_1 HAS SELECTED Operator_3  

4	6			2				// Agent_2 HAS SELECTED Operator_1  
5	6			3				// Agent_2 HAS SELECTED Operator_2  

6	7			3				// Agent_3 HAS SELECTED Operator_2 
7	7			4				// Agent_3 HAS SELECTED Operator_3

8	8			2				// Agent_4 HAS SELECTED Operator_1  
9	8			4				// Agent_4 HAS SELECTED Operator_3 
*/	

CREATE TABLE `login_users` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_level` INT(1) NOT NULL DEFAULT '3',
  `cname` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `selected_operators` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `operator_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `login_users` (`user_id`, `user_level`, `cname`)
VALUES
(1, 1, 'ADMIN'),
(2, 2, 'Operator_1'),
(3, 2, 'Operator_2'),
(4, 2, 'Operator_3'),
(5, 3, 'Agent_1'),
(6, 3, 'Agent_2'),
(7, 3, 'Agent_3'),
(8, 3, 'Agent_4');


INSERT INTO `selected_operators` (`id`, `user_id`, `operator_id`)
VALUES
(1, 5, 2),
(2, 5, 3),
(3, 5, 4),
(4, 6, 2),
(5, 6, 3),
(6, 7, 3),
(7, 7, 4),
(8, 8, 2),
(9, 8, 4);


SELECT DISTINCT(so.user_id), lu.cname FROM selected_operators AS so
INNER JOIN login_users AS lu
ON so.user_id = lu.user_id 
WHERE  so.user_id 
NOT IN (SELECT DISTINCT user_id FROM selected_operators WHERE operator_id = 3) 
AND lu.user_level = 3
ORDER BY so.user_id;

 

Thanks,

 

Tim

 

oh and if it has any influence i'm currently using MySQL 5.5.9

[kickstart]

Hi

 

Not tested, but give something like this a try, which avoids having a subselect and moves some of the selection of rows to the ON clauses.

 

SELECT DISTINCT(so.user_id), lu.cname 
FROM selected_operators AS so
INNER JOIN selected_operators AS so2 ON so.user_id = so2.user_id AND so2.operator_id = 3
INNER JOIN login_users AS lu ON so.user_id = lu.user_id AND lu.user_level = 3
WHERE  so2.user_id IS NULL
ORDER BY so.user_id;

 

All the best

 

Keith

[mikosiko]

try this:

 

SELECT a.user_id, a.cname
  FROM login_users a
   LEFT JOIN selected_operators b ON a.user_id = b.user_id AND b.operator_id = 3
WHERE a.user_level = 3
AND b.operator_id IS NULL;

 

Adding an INDEX on the column user_id  in the table selected_operators should help too.

[timw]

Thanks for your responses guys,

 

Keith, after a quick look yours was returning 0 rows on my larger database so not quite sure why... i need to read through and see where yours is going there.

 

mikosiko, Thats great runs a lot quicker on the larger database and also gives me a bonus: The agents that have not selected any operators at all now show too, I cant quite work out why that is yet but will have a bit more of a read in a little while.

 

Sorry for being thick what do you mean by "Adding an INDEX on the column user_id  in the table selected_operators should help too."

 

 

Thanks again,

 

Tim

[mikosiko]

Adding an INDEX on the column user_id  in the table selected_operators should help too

 

You table selected_operators has only a PK (Primary Key) on the column `id`, however you need to JOIN that table with the table login_users using the common column user_id, therefore adding an INDEX on that column in the table selected_operators will improve the query performance.

 

use whatever tool that you are using now to add that index in your table.... basically:

 

ALTER TABLE `selected_operators` ADD INDEX `name-of-your-index-here`(`user_id`);