Is there any difference, performance wise, with these two queries? (Repeating the where clause inside the sub-query) MYSQL

Question

I have a query that goes something like this.

Select *
FROM FaultCode FC
JOIN (
    SELECT INNER_E.* FROM Equipment INNER_E

) E USING(EquipmentID)
LEFT JOIN AssetType AT ON AT.id_asset_type = E.id_asset_type AND AT.id_language = 'en-us'
LEFT JOIN Project P ON E.current_id_project = P.id_project
WHERE E.id_organization = 100057 AND E.equipment_status = 'ACTIVE'
    AND FC.code_status = 'OPEN'

As you can see, in the outside query, there is a where clause in the outside main query. But also, on the inside, we have an Inner Join statement with the line SELECT INNER_E.* FROM Equipment INNER_E. This inner join makes us only retrieve the fault codes that are inside the equipment table (correct me if I'm wrong).

I am trying to optimize this query.

My question is, does it make any difference to do this

Select *
FROM FaultCode FC
JOIN (
    SELECT INNER_E.* FROM Equipment INNER_E
    WHERE INNER_E.id_organization = 100057 AND INNER_E.equipment_status = 'ACTIVE'

) E USING(EquipmentID)
LEFT JOIN AssetType AT ON AT.id_asset_type = E.id_asset_type AND AT.id_language = 'en-us'
LEFT JOIN Project P ON E.current_id_project = P.id_project
WHERE E.id_organization = 100057 AND E.equipment_status = 'ACTIVE'
    AND FC.code_status = 'OPEN'

So repeating the where clause inside the inner sub query, to further limit it before it joins. Or does the optimizer know to do this automatically?

I tried implementing that line in code, and it seemed to only make my query slower strangely enough. Is there any way I can optimize that query above, or since it's pretty simple, is that the best it's going to get without indexes?

I tried running the Explain Select statement, but I have a hard time parsing what it's telling me. Are there any good resources I can look into to learn some tips or techniques to optimize my query?

I don't have any aggregate functions in my Select fields. So is the only real answer Indexes?

You might want to move your inner query to a WITH clause and see if that helps. https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — computercarguy, Dec 10 '21 at 22:06

score 0 · Answer 1 · answered Dec 11 '21 at 06:15

Why is the first subquery needed? Perhaps simply

Select  *
    FROM  FaultCode FC
    JOIN  Equipment AS E  USING(EquipmentID)
    LEFT JOIN  AssetType AT  ON AT.id_asset_type = E.id_asset_type
      AND  AT.id_language = 'en-us'
    LEFT JOIN  Project P  ON E.current_id_project = P.id_project
    WHERE  E.id_organization = 100057
      AND  E.equipment_status = 'ACTIVE'
      AND  FC.code_status = 'OPEN';

Likely Indexes:

FC:  INDEX(code_status, EquipmentID)
E:  INDEX(id_organization, equipment_status, EquipmentID,)

Probably unwise to do SELECT * -- It will give you all the columns of all 4 tables. (Without further details, I cannot suggest any "covering" indexes, which seems likely for AT.)

With my version of the query, your question about repeating the WHERE vanishes. With your version, it is likely to help. I don't think the Optimizer is smart enough to catch on to what you are doing.

Show us the EXPLAINs. We can help some with what the cryptic stuff is saying. (And what it is not saying.)

"the best it's going to get without indexes" -- Are you saying you have no indexes??! Not even a PRIMARY KEY for each table? "So is the only real answer Indexes?" Every time you write a query against a non-tiny table, you should ask "do the table(s) have adequate indexes for this query?"

Is there any difference, performance wise, with these two queries? (Repeating the where clause inside the sub-query) MYSQL

1 Answers1