MySQL Forums
Forum List  »  Optimizer & Parser

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3 Table Join (indexed) results in where-temp-filesort
4064
April 30, 2009 08:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.