3 Table Join (indexed) results in where-temp-filesort
Posted by:
Doug Crane
Date: April 30, 2009 08:21PM
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