Left join taking too long.

harman

HI,
when i run the following query it takes aprox 3+min for the page to load:
$query = "SELECT c.cust_id, c.f_name, c.l_name, DATE_FORMAT(c.signup_date, '%m-%d-%y') AS s_date, d.company, d.email FROM customer c LEFT JOIN customer_detail d ON c.cust_id=d.cust_id where company like '".$lletter."%' AND is_active = 'Y' order by company";

but when i take out the join and refrence only one table with the following query it takes about 3 sec:
$query = "SELECT cust_id, company, email FROM customer_detail WHERE company LIKE '".$lletter."%' ORDER BY company";

I need info from both tables so is there a better way to do this...
thanks

paulnaj

Hi harman,

I tweaked your sql to look like ...

$query = "
SELECT c.cust_id, c.f_name, c.l_name
  , DATE_FORMAT(c.signup_date, '%m-%d-%y') AS s_date
  , d.company, d.email 
FROM customer c 
LEFT JOIN customer_detail d ON c.cust_id=d.cust_id 
WHERE company LIKE '".$lletter."%' 
AND is_active = 'Y' 
ORDER BY company
";

... to see what's going on.

A couple of things might be causing things to slow down ...

You've left out AS in the FROM clause. I would have thought that that was essential, but it might still slow things down.
Similarly, you've left out AS in the LEFT JOIN clause.
In the WHERE clause and the ORDER BY clause, you could put c.company (or d.company).
Similarly, in the AND clause, you could put c.is_active (or d.is_active).
[/list=1]

Actually, I never use the LEFT JOIN method, I just use the WHERE clause. (Getting rid of the LEFT JOIN shouldn't really make any difference to the speed though, because this is an equivalent statement, but for me, it's simpler).

With all that in mind, is this ...
```
$query = "
SELECT c.cust_id, c.f_name, c.l_name
  , DATE_FORMAT(c.signup_date, '%m-%d-%y') AS s_date
  , d.company, d.email 
FROM customer AS c, customer_detail AS d 
WHERE c.cust_id = d.cust_id 
AND c.company LIKE '".$lletter."%' 
AND c.is_active = 'Y' 
ORDER BY c.company
";
 
```
... any good?

Paul.

Drakla

Do you have any indexes on the tables? Getting one of cust_id would be a real nice start, and a short one on the company

tsinka

Hi,

execute the following query on your MySQL database:

EXPLAIN <query>

That will return records with information about how MySQL optimizes the query and which indexes MySQL will use. You can use the output to determine the indexes you need to create.

I think you'll come to the conclusion that the indexes Drakla mentioned will be sufficient. Creating an index on is_active doesn't make much sense if that field only holds Y and N values.

Thomas

harman

If i run it without the join ie
FROM customer AS c, customer_detail AS d
WHERE c.cust_id = d.cust_id
like paulnaj suggested it runs the query in no time, i dont understand why left join should take so long...anyways thanks paulnaj, tsinka and Drakla... i guess i should try and keep thinks simple

paulnaj

Hi harman - glad it worked 🙂

Hey drakla, I'm a bit hazy on indexes :queasy: (just posted a question recently MySQL Indexes ).

You said - 'Do you have any indexes on the tables? Getting one of cust_id would be a real nice start, and a short one on the company' - can I ask what you mean by a 'short one'?

Cheers,
Paul.

harman

drakla i would also like to know more about index, can you send me a good tutorial link...thanks