join query slowing down the server

[php_begins]

I was running this query and feel this is slowing down the server:

 

$agent_query=$this->db->query("
                       SELECT u.id,u.name,u.team_id,l.agent_id from users AS u,leads AS l WHERE u.id=l.agent_id AND u.team_id 
                        IS NOT   NULL AND u.is_active='1'
		ORDER BY u.name ASC
	");

 

Is there a better way to write the above code.

[The Little Guy]

First, format your query properly, the way your joining is supported, but I don't think it is an optimized format (I could be wrong).

 

SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

 

Second, place an "EXPLAIN" in front of your "SELECT"

 

EXPLAIN SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

 

You "Key" column should have a key in it, if it is "NULL" that might be your problem.

[AyKay47]

First, format your query properly, the way your joining is supported, but I don't think it is an optimized format (I could be wrong).

 

SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

 

Second, place an "EXPLAIN" in front of your "SELECT"

 

EXPLAIN SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

 

You "Key" column should have a key in it, if it is "NULL" that might be your problem.

 

if you really wanted to test and compare both queries, you could run each one using the EXPLAIN statement and compare the performance results.

[php_begins]

Thanks guys..both the queries is literally stopping down the server.

 

This is the result of explain statement for left join(phpmyadmin):

I dont know what it means.

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE u ALL NULL NULL NULL NULL 49 Using where; Using filesort

1 SIMPLE l ref agent_id agent_id 5 u.id 1754 Using index

 

I could not run an explain in my simple join query as i am waiting for someone to kill the query. The server is not responding.

 

 

[AyKay47]

mysql has a nice little extension of the EXPLAIN statement call EXPLAIN EXTENDED, post the results of this test:

 

EXPLAIN EXTENDED SELECT u.id,u.name,u.team_id,l.agent_id FROM users AS u LEFT JOIN leads AS l ON(u.id=l.agent_id) WHERE u.team_id 
IS NOT NULL AND u.is_active='1'
ORDER BY u.name ASC

[php_begins]

Here's what I got back. Can u please tell me what it means:

 

id select_type table type possible_keys key key_len ref rows filtered Extra

1 SIMPLE u ALL NULL NULL NULL NULL 49 100.00 Using where; Using filesort

1 SIMPLE l ref agent_id agent_id 5 consumer_united.u.id 3412 100.00 Using index