Optimize MySQL query multiple joins to check the same flag in each table-CodePudding

I am trying to optimize a query (by not changing what it does).

This is the query

SELECT g.id FROM ci_Groups AS g 
LEFT JOIN ci_PartGroupings AS pg ON pg.GroupID=g.id 
LEFT JOIN ci_partmaster AS pm ON pg.PartMasterID=pm.id 
LEFT JOIN ci_PartPriceInv AS ppi ON ppi.PartMasterID=pm.id 
LEFT JOIN ci_PartToAppCombo AS ptac ON pm.id=ptac.PartmasterID 
LEFT JOIN ci_ApplicationCombination AS acON ac.id=ptac.ApplicationComboID 
LEFT JOIN ci_Fitment AS fit ON fit.PartToAppComboID=ptac.ID 
LEFT JOIN ci_TypeMakeModelYear AS tmmy ON tmmy.id=fit.TMMYID 
LEFT JOIN ci_Models AS md ON tmmy.ModelID=md.id 
LEFT JOIN ci_Makes AS mk ON tmmy.MakeID=mk.id 
LEFT JOIN ci_Years AS y ON tmmy.YearID=y.id 
WHERE g.UpdateFlag <> '' OR pg.UpdateFlag <> '' OR pm.UpdateFlag <> '' OR ppi.UpdateFlag <> '' OR ptac.UpdateFlag <> '' OR ac.UpdateFlag<> '' OR fit.UpdateFlag <> '' OR tmmy.UpdateFlag <> '' OR md.UpdateFlag <> '' OR mk.UpdateFlag <> '' OR y.UpdateFlag <> '' 
GROUP BY g.id;

The query is joining multiple tables just to be able to check if there are any updates in those tables.

Is there any better way of doing it, with doing simple adjustment? I was thinking about adding index on all UpdateFlag fields in each table that was joined, but I am wondering if there is any better solution to have this query logic improved.

Thanks for any tip/advice!

CodePudding user response：

Instead of just having a flag on each table, also have a flag on the main table, ci_Groups. Set that new flag via a TRIGGER on each of the other tables.

Then you can SELECT ... FROM ci_Groups WHERE UpdateFlag <> ''. If that does not run fast enough, we can discuss whether to have INSER(UpdateFlag).

Does UpdateFlag get turned off at some point? If so, then that would need to be part of the processing. (Since I don't understand the details of that flag, I can't predict when, exactly, it should be done.)

CodePudding user response：

SELECT t1.x
FROM t1
LEFT JOIN t2
WHERE t1.y <> '' OR t2.z <> ''

can be replaced with

SELECT t1.x
FROM t1
WHERE t1.y <> ''

UNION ALL

SELECT t1.x
FROM t1
INNER JOIN t2
WHERE t1.y = '' AND t2.z <> ''

Do this with all JOINs. The query will be monstrous but more fast.