Optimization in nested MySQL query possible?

Question

I have the following table (simplified, removed unneeded columns):

packageTransactions

| memberID  |  subscriptionType |
| ==========|===================|
| 12345     |  101              |
| 12345     |  203              |
| 12346     |  101              |
| 12347     |  101              |
| 12348     |  101              |
| 12348     |  203              |
| 12349     |  203              |
| 12345     |  205              |

I want to query all records which have not subscriptionType = 101, but only those where a record for subscriptionType = 101 for the same memberID exists.

Therefore, I use:

SELECT memberID, subscriptionType
  FROM packageTransactions
 WHERE memberID IN
    ( SELECT memberID
        FROM packageTransactions
       WHERE subscriptionType = '101'
    )
   AND subscriptionType <> '101'
;

which gives me the resultset that I'm looking for:

| memberID  |  subscriptionType |
| ==========|===================|
| 12345     |  203              |
| 12348     |  203              |
| 12345     |  205              |

However, when using this query on a table with a few thousend records (+30k in my case), it takes minutes to return the result.

So I wonder, if there's a "better" / more efficient way to query the data?

SELECT memberID, subscriptionType FROM packageTransactions WHERE subscriptionType != '101'. Did you try this why are you using where in? — Muhammad Raheel
– Muhammad Raheel, Commented Dec 20, 2012 at 8:19
Sorry, english is not my native language. I tried to made it more clear in the question now. Because I want to get only those memberIDs, where also a record for subscriptionType = '101' generally exists. memberID 12349 for example should not be returned, because it has no record for subscriptionType = '101'. — eyecatchUp
– eyecatchUp, Commented Dec 20, 2012 at 8:24

Omar Freewan · Accepted Answer · 2012-12-20 08:19:43Z

1

Try this :

SELECT pt2.memberID, pt2.subscriptionType
FROM packageTransactions pt1 inner join packageTransactions pt2 
   ON pt1.memberID = pt2.memberID  
WHERE 
  pt1.subscriptionType = '101' AND pt2.subscriptionType <> '101'

;

answered Dec 20, 2012 at 8:19

Omar Freewan

2,6784 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

valex · Accepted Answer · 2012-12-20 08:31:12Z

1

Here is a SQLFiddle demo

select t.* from packageTransactions t
join
(
  select distinct memberID 
    from packageTransactions
  where subscriptionType = 101 
) t1 on t.memberId= t1.memberId
where
  (subscriptionType <> 101)

answered Dec 20, 2012 at 8:31

valex

24.2k7 gold badges47 silver badges62 bronze badges

1 Comment

eyecatchUp Over a year ago

Thank you. Both, yours and Omar's, solutions are significant faster. However, now I wonder which query is more suitable and - even more important to me - why?

Collectives™ on Stack Overflow

Optimization in nested MySQL query possible?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related