left join optimization

[webmaster]

I am having a strange problem understanding an optimization issue.

I have a query that runs:

SELECT p.id as id
FROM table1 as m
LEFT JOIN table2 AS p ON m.id=p.table1_id
LEFT JOIN table3 as f ON m.id=f.table1_id

This takes upwards of 150 seconds to run on the webserver in my PHP page, and seems to timeout in phpmyadmin.

if I limit this statement to:


SELECT p.id as id
FROM table1 as m
LEFT JOIN table2 AS p ON m.id=p.table1_id

I get a rapid response time like:
Showing rows 0 - 29 (53346 total, Query took 0.0035 sec)

even if I get everything at once, the response is similar to:

Showing rows 0 - 53345 (53346 total, Query took 12.2941 sec)

Initially, table3.table1_id did not have an index set on it, and I added one via phpmyadmin.

table3 only has 400 records and I cannot understand this large jump in execution time.


This is running MySQL 3.23.49.


Does anyone have any suggestions?

As a side note, I have worked with SQL structure quite a bit, but as for mySQL and query optimization I could use some suggestions on good books and reference material.


Thanks everyone