Need help with left jons

[efron]

I am not a sql coder, so forgive any stupid mistakes in the code below. It runs slower than slow when executes (10-20 minutes). Where have a gone wrong, it bring out the information I need, just unbelievalby slowly...

SELECT leads.lname, leads.fname, leads.phone, leads.email, 
leads.afid, leads.age, leads.address1, leads.address2, leads.city, 
leads.state, leads.zip, date_format(leads.date, '%m-%e-%Y'), 
leads.altphone, leads.timestamp, affiliates.site_name,
programs.description, optinleads.selected, optinleads.batch
optinleads.progvalue 
FROM leads 
LEFT JOIN affiliates on leads.afid=affiliates.afid 
LEFT JOIN optinleads on leads.leadid=optinleads.leadid 
LEFT JOIN programs on optinleads.progid=programs.progid 
WHERE optinleads.batch = 65 
ORDER BY timestamp

[tomhath]

Try changing the order of the joins and putting some indexes on the tables. I obviously can't test these:

create index lid on optinleads (leadid)
create index pid on programs (progid)
create index aafid on affiliates (afid)

SELECT leads.lname, leads.fname, leads.phone, leads.email,
leads.afid, leads.age, leads.address1, leads.address2, leads.city,
leads.state, leads.zip, date_format(leads.date, '%m-%e-%Y'),
leads.altphone, leads.timestamp, affiliates.site_name,
programs.description, optinleads.selected, optinleads.batch
optinleads.progvalue
FROM leads
INNER JOIN optinleads
ON leads.leadid=optinleads.leadid
AND optinleads.batch = 65
LEFT JOIN programs on optinleads.progid=programs.progid
LEFT JOIN affiliates on leads.afid=affiliates.afid
ORDER BY timestamp

[efron]

Thanks, I'll give it a shot. Those already are indexed, by the way.