mysql query performance... got to be a better way

Question

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?

Answer 1

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`;

Answer 2

Here is a simplified query for that

first get the min AssociatedObjectID as first object and then get other objects.

select 
  t3.AssociatedObjectID first_object,
  t.AssociatedObjectID
from test t
    join (
        select 
          t2.AssignmentSubType,
          t2.AssignmentValue,
          t2.OwnerUsergroupID,
          min(t2.AssociatedObjectID) as AssociatedObjectID
        from test t2
        group by
          t2.AssignmentSubType,
          t2.AssignmentValue,
          t2.OwnerUsergroupID
    ) t3 
    on t.AssignmentSubType = t3.AssignmentSubType
    and t.AssignmentValue  = t3.AssignmentValue
    and t.OwnerUsergroupID  = t3.OwnerUsergroupID
    and t.AssociatedObjectID  != t3.AssociatedObjectID;