Why does adding "left" to my join make the MySQL query hang?

Question

This query executes in a fraction of a second:

SELECT customers.customers_id, customers_firstname, customers_lastname, customers.customers_email_address, max(date_purchased) 
FROM customers join orders on customers.customers_id = orders.customers_id 
group by customers.customers_id;

If I change the join to a left join, it seems to hang. I tried limiting it to 10 records, and it still takes 9 seconds. What am I doing wrong?

Thanks in advance.

Answer 1

Do you have an index created on the join criterias?

Is customers_Id indexed on the orders table?

you can see if there are any indicies on the table with the following

SHOW INDEXES FROM Orders;

To create an index

CREATE INDEX ix_order_customersId ON Orders (customers_id);

Answer 2

for investigating the problem, you could try EXPLAIN SELECT ....., that will list you what are the indexes/cardinality of the request and why it is taking that much time.