Mysql – thesql query performance… got to be a better way

MySQLoptimizationperformance

I have a query right now that for a lack of a better word, sucks. I'm looking for some help with respect to other ways to approach writing this query. Here are the details.

I've got a temp table build up with the following columns:

  • AssignmentSubType
  • AssignmentValue
  • OwnerUsergroupID
  • AssociatedObjectID

The basic gist of what I want to do is to find AssociatedObjectIDs that have the same AssignmentSubType, AssignmentValue, and OwnerUsergroupID. This is because there is a ton of SQL down the road that really doesn't need to be run if I've got "duplicates" (same AssignmentSubType, AssignmentValue, and OwnerUsergroupID). Once I've got my list of duplicates I run the next set of SQL stuff on one of the duplicates , when I've got back info from that I join back the rest of the duplicates for the final result set.

Example table data:

AssignmentSubType | AssignmentValue | OwnerUsergroupID | AssociatedObjectID
retailer          | Dicks           | 1                | 5
retailer          | Dicks           | 1                | 7
retailer          | Dicks           | 1                | 9

In this example I just want to do the calculations on associatedobjecid = 5 since 7 and 9 will be the exact same, and then join back in 7 and 9 at the 'end'

I've got the following query that works but is very inefficient

SELECT firstObject, f2.AssociatedObjectID 
            FROM ( 
                SELECT firstObject, dupAss AS dups 
                FROM ( 
                    SELECT min(AssociatedObjectID) AS firstObject, group_concat(concat('-',AssociatedObjectID,'-')) AS dupAss 
                    FROM ( 
                        SELECT * FROM tableFromAbove 
                    ) innerRes GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID 
                ) outR 
            ) outR2 
            LEFT JOIN tableFromAbove f2 
                ON outR2.dups LIKE concat('%-',f2.AssociatedObjectID,'-%') ORDER BY firstObject

this query will give me back a result set like the following

firstObject | AssociatedObjectID
5           | 7
5           | 9

like I said earlier in this post… I then use this result set to join back to the results from the rest of the queries for associatedobjectid = 5. Any thoughts on how I could restructure this so that it is more efficient?

Best Answer

this is josh. Try the following 

SELECT  b.minID, a.AssociatedObjectID
FROM    tableFromAbove a
        LEFT JOIN
        (
            SELECT AssociatedObjectID, AssignmentSubType, AssignmentValue, OwnerUsergroupID, MIN(AssociatedObjectID) minID
            FROM tableFromAbove 
            GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID
        ) b
            ON a.AssignmentSubType = b.AssignmentSubType AND a.AssignmentValue = b.AssignmentValue AND a.OwnerUsergroupID = b.OwnerUsergroupID
            WHERE minID <> a.`AssociatedObjectID`;
Related Question