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

[Rick James]

Classic problem. JOIN with WHERE filtering on one table; ORDER BY using another. The problem is that it has to haul around a bunch of data from one table to the other (via the link table) before it can finish filtering/ordering.

The solution makes "normalization" advocates cringe. You need to get the WHERE and the ORDER BY in the same table. The 'cringe' comes because there will be redundant data.

The extra table needs to be faithfully maintained as the existing tables are INSERTed/DELETEd. In your case, it may be as simple as adding dateFiled to the party_case_link table, plus having
INDEX(party_id, dateFiled)

The join to get from firmId to party_id (assuming index on party.firmID) is not a performance concern.

(LIMIT & OFFSET -- smells like pagination. Once you are happy with page 1, come back and I will explain why accessing page 100 is so inefficient.)