3 Table Join (indexed) results in where-temp-filesort

[Doug Crane]

Howdy all -- I'm stuck on how to make my 3 table join zippy. I've created a simplified view of the table structure for us to look at:

court_case
-----------------------
id (pk)
caseNumber
dateFiled


party_case_link
-----------------------
case_id (pk)
party_id (pk)


party
-----------------------
id (pk)
firmId
firstName
lastName

I have indexes where I think the belong.. I did add additional indexes on columns used in where clauses or order bys (foo, bar in these examples), but I dont think they would cause this...

The following works like a champ:

EXPLAIN SELECT
party.id AS partyId,
party.firm_user_id AS firmUserId,
party.firm_id AS firmId,
court_case.date_filed AS dateFiled,
court_case.id AS caseId
FROM court_case
JOIN party_case_link
ON (party_case_link.case_id = court_case.id)
JOIN party
ON (party.id = party_case_link.party_id)
WHERE
party.firm_id = 1
ORDER BY party.last_name ASC
LIMIT 25 OFFSET 0;

NO problems -- all indexes.. Clean and fun.. BUT

If I attempt to order by elements of the court_case table I get the dreaded where;temp;filesort..

EXPLAIN SELECT
party.id AS partyId,
party.firm_user_id AS firmUserId,
party.firm_id AS firmId,
court_case.date_filed AS dateFiled,
court_case.id AS caseId
FROM court_case
JOIN party_case_link
ON (party_case_link.case_id = court_case.id)
JOIN party
ON (party.id = party_case_link.party_id)
WHERE
party.firm_id = 1
ORDER BY court_case.date_filed ASC
LIMIT 25 OFFSET 0;

There is an index on date_filed. Standalone -- should it be something / somewhere else?

I also added indexes on the two individual fields on the party_case_link table. That greatly sped up the temp - filesort (teehee..) but doesnt avoid it.

I have read up on a number of blogs / postings and read that I might need to add what I'm attempting to ORDER BY to my WHERE clause.. I tried that, but it doesnt help (I tried WHERE date_filed IS NOT NULL and WHERE date_filed < NOW())

Amazingly if I set a *specific* date in the WHERE clause it does use indexs and all is well. the problem is that I dont what just that one day :)

This works for 1 day of data (doesnt help me IRL, but maybe a clue as to what;s wrong):

SELECT
party.id AS partyId,
party.firm_user_id AS firmUserId,
party.firm_id AS firmId,
court_case.date_filed AS dateFiled,
court_case.id AS caseId
FROM court_case
JOIN party_case_link
ON (party_case_link.case_id = court_case.id)
JOIN party
ON (party.id = party_case_link.party_id)
WHERE
court_case.date_filed = '2009-01-02'
AND
party.firm_id = 1
ORDER BY court_case.date_filed ASC
LIMIT 25 OFFSET 0;

Thoughts, suggestions, comments? What else can I provide which can lead me to the promised land of zippy mysql?

Thanks in advance,

D