i've got the query optimization blues ... stumped

[Joe Simone]

I have the folling query, for which I have created indices for the fields I am joining on. However, when I do an EXPLAIN, I get an ALL for the join type for one of the left joins. The query runs super slow and I am at a loss as to what I should or can do. Can anyone shed some light?

For the e2m.person p table I get 4 possible keys -- NONE of which are used. Why?

Don't really know what to do.
Thanks for any help in advance,
Joe

--------------------------------------------

SELECT
temp.lastname "Last Name",
temp.firstname "First Name",
temp.personalId "GID",
temp.rtname "Track",
#temp.declined,
#temp.denied,
#temp.invited,
date(temp.created) "Created",
CASE
when (temp.denied is not null) then 'Denied'
when (sum(temp.registered) > 0) then 'Registered'
when (temp.declined is not null) then 'Declined'
when (temp.invited is not null) then 'Invited'
else 'Pending'
END 'Status'
FROM
( SELECT
i.id "iid",
i.emailAddress,
i.firstname,
i.lastname,
i.companyname,
i.personalId,
i.remarks,
i.comments,
su.uid "suid",
e.id "eid",
e.name "ename",
rt.code "rtcode",
rt.name "rtname",
rt.id "rtid",
i.mailings_count,
i.declined,
i.denied,
i.invited,
i.created,
CASE
WHEN (e.id IS NOT NULL AND e.id = il.event_id) THEN 1
ELSE 0
END "registered"
FROM
e2m.invitationlist il,
e2m.invitation i LEFT JOIN e2m.regtype rt ON i.regType_id = rt.id
LEFT JOIN e2m.systemuser su ON i.emailAddress = su.emailAddress
LEFT JOIN e2m.person p ON (p.systemuser_uid = su.uid or
i.personalId = p.companySerial)
LEFT JOIN e2m.event e ON p.event_id = e.id
WHERE
il.event_id = 'k60ef344b71bc2510e1f67a796' AND
i.invitationlist_id = il.id
) temp
GROUP BY
temp.emailaddress
ORDER BY
#'Status',
temp.lastname,
temp.firstname